Finding duplicate data ona three field index

[epb]

I have a database that needs to have a three field index created to prevent duplicate entries. Problem is that there is already thousands of records and some of these have duplicates across the three fields.

I created the three field index and on saving the table it would not allow the save because there are already duplicates across the three fields.

My question is how do I create a query to display these duplicates. The table is [CustOrders]. The fields are [POPart], [PONum] and [POLine]. I believe most of the issue is with [POLine] being duplicated. I just need to see the duplicates or print out their OrderID number so I can look at the record. OrderID is the PK.

[June7]

Start with an aggregate (GROUP BY) query that counts the occurrence of each 3 field combination.

SELECT [POPart], [PONum], [POLine], Count(*) As CountKey FROM [CustOrders] GROUP BY [POPart], [PONum], [POLine] WHERE Count(*) > 1;

If you want to see the OrderID, join that aggregate query to CustOrders table by linking on the 3 fields.

[epb]

I am not sure how that is working. I am not too knowledgeable in SQL. I tried putting that line in a query and it says there is an issue with a missing operator and gives the following code:

'[POLine] WHERE Count(*) > 1'

I am not sure how I join the [OrderID] to that also. I am crawling and not walking when it comes to SQL.

[June7]

That's what I get trying to create from memory without testing syntax.
Instead of WHERE, use HAVING.

The join will not be on OrderID. In another query, join the aggregate query with the table. The join will be on the 3 compound key fields.

[epb]

Thank you June! Using "HAVING" worked...I did not use the JOIN because the handful or records that were duplicates could be found easily without it.

Thanks again