Daily Cup of Tech

I am working on a project and I am storing large amount of data in Access.  I needed a quick an easy way to remove duplicate records based on a specific field in one of my tables.  I thought, “This shouldn’t be too hard since there is a query wizard to determine duplicate records.” 

Man, was I wrong!

After about a day of trying, I figured out the following little trick:

1. Make a copy of your original data table but just copy the table structure, not the data.
2. Edit the new table and change the field with the duplicates’s index to No Duplicates Allowed.
3. Save the new table.
4. Create an append query that will append all of the data from the original table to the new table.
5. Run the append query.  You will see a number of warnings/errors.  Simply accept them to complete the process.
6. Rename the original table.
7. Rename the new table with the same name as the original table.

You should now have all the duplicate records gone!

Trackback link - http://www.dailycupoftech.com/2009/03/29/remove-duplicate-records-in-access/trackback/