Find Duplicate queries

[adrianvasile]

I was wondering if anyone has any experience. I am trying to create a query that will find duplicate values to prevent duplicate orders entering into our system. So i have a table with ACCT#, ITEM, QTY and DATE. With every order, there will be listing with the ACCT, ITEM , etc.
I would like to be able to generate a query that will list all accounts that have duplicate items; let's say for example:

ACCT ITEM QTY DATE
1234 0001 1 01/01/09
1234 0002 2 01/01/09
2222 0056 3 01/01/09
2222 0057 1 01/01/09
2222 0056 4 01/01/09

i would like the query to return:

ACCT ITEM QTY DATE
2222 0056 3 01/01/09
2222 0056 4 01/01/09

I tried the query wizard but no luck. Also, I need to mention that I will need to add some extra criteria to the query - exclude negative orders and to match a certain date. I think that the problem arise when you add additional criteria.
Thank you for any insight.

 

[PHV]

A starting point:

SELECT ACCT, ITEM 
FROM yourTable
GROUP BY ACCT, ITEM
HAVING Count(*)>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adrianvasile]

I think it worked - why I did not see it?