Finding Duplicate Records in a Table 1

[mrussell71]

How do I find which records are duplicated in an Access table?

I have been removing duplicate records from tables by creating a copy of the original table (structure only) then set each field in the nonduplicate table as a primary key. I create an Append query using the original table and append these records to the nonduplicate table. If there are duplicate records then I get Key Errors which removes the duplicate records.

Now I have two tables, Original and NonDuplicate. Is there a way to ascertain which records were duplicated and dump them into a separate table?

Thanks.

 

[Shirley]

If you are using Access 97 and 2000 you could create a duplicate query. Where you select the duplicate field or fields that you would like to check for.

 

[MrSeAbeE]

The easiest way is in stead of running an update query, just copy all the records from the duplicates table into the new table with the unique key, Access will automatically paste the duplicate rows in to a new table called “Paste Errors”. I know it’s not the most fancy way but it works.