Find Duplicates Query

[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.

 

[randy700]

How about the "Find Duplicates Query Wizard"?


Randy

 

[adrianvasile]

I tried. What it does it will list all the duplicate accounts AND all items for that account. I want to list each account with any duplicates if any.
When I ran the wizard it came up like this:


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

 

[randy700]

So, you have a concatenated key?
You will have to modify the query to account for this.


Randy