Find duplicate records

[pwbrown]

What is the best way to find duplicate records in a table with 25 columns!?

I know there is a built in find duplicate query but it only allows a max of 10 columns to be compared.

Edit: I actually want to find duplicates records between two tables both the same layout and columns

Any help welcome
Kind regards,

Peter


p.s.
Sorry if this thread gets posted twice, there was an error when I first tried to post it.

 

[pwbrown]

Ok I scrapped this method and just added a unique field which will solve the problem but would still be good to know how to do this.

 

[plog]

When talking about data, like the word 'unique'; 'duplicate' isn't always used by people the same way the dictionary defines it. Since I don't know what you exactly intend by that word when referencing your data, I will say this is how I would do it by the classical meaning of 'duplicate':

I would create a query and bring down every field of your table, I would then make it an aggregate query (by clicking the Sigma) and GROUP BY every field. Next I would bring down a field I know will be populated in the data and COUNT BY that field (this means that field will appear twice, once using GROUP BY, once using COUNT). Lastly I would sort ascending on that counted field so that duplicated values would be the first ones listed. Run the query and any rows that have a COUNT greater than 1 are duplicates.