Create a Query in Microsoft Access to Find Duplicate Entries in a Table

This article was written by MysticGeek, a tech blogger at the How-To Geek Blogs.

Running into duplicate data in an Access Database can be quite annoying during the hectic workday, so I decided to write up a technique to help identify the duplicate records so you can manually handle them.

There are many causes for duplicate data: for instance if you have multiple users entering data simultaneously via linked tables, or you might inherit a table with duplicate data already entered.

First open the MDB (Microsoft Database) containing the table you want to check for duplicates.  Click on the Queries tab and New.

1 

This will open the New Query dialog box.  Highlight Find Duplicates Query Wizard then click OK.

1 

Now highlight the table you want to check for duplicate data.  You can also choose Queries or both Tables and Queries.  I have never seen a use for searching Queries … but perhaps it would come in handy for another’s situation.  Once you’ve highlighted the appropriate table click Next.

1

Here we will choose the field or fields within the table we want to check for duplicate data.  Try to avoid generalized fields.

1

Name the Query and hit Finish.  The Query will run right away and pop up the results.  Also the Query is saved in the Queries section of Access.

2

Depending upon the selected tables and fields your results will look something similar to the shots below which show I have nothing duplicated in the first shot and the results of duplicates in the other.

1           1

Brian Burgess worked in IT for 10 years before pursuing his passion for writing. He's been a tech blogger and journalist for the past seven years, and can be found on his about me page or Google+