I have a question about how one would go about finding Duplicate dollar amounts with different supplier ID's. I know how to find the duplicate dollar amounts as displayed below:
FROM tblSales
WHERE (((tblSales.Amount) In (SELECT [Amount] FROM [tblSales] As Tmp GROUP BY [Amount] HAVING Count(*)>1 )))
ORDER BY tblSales.Amount;
The issue I get with this is that it returns all the duplicates include ones from the same supplier. How would I then specify that I only want duplicate Amounts where the Supplier ID are different?
Code:
SELECT tblSales.Amount, tblSales.SupplierID FROM tblSales
WHERE (((tblSales.Amount) In (SELECT [Amount] FROM [tblSales] As Tmp GROUP BY [Amount] HAVING Count(*)>1 )))
ORDER BY tblSales.Amount;
The issue I get with this is that it returns all the duplicates include ones from the same supplier. How would I then specify that I only want duplicate Amounts where the Supplier ID are different?