How-To Geek

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.


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


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.


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


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.


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+

  • Published 04/16/08

Comments (5)

  1. maheshexp

    This is really a awesome feature that I’ve never came across!!

  2. maheshexp

    Is that NumberOfDupes introduced automatically by the Access?
    If I have more than 4 columns and all the fields have dupes, how the it will display the results?

  3. mrkerr

    This site is a great resource! It helps us geeks learn new tricks and sometimes remind us of some we have forgotten. You might search a query for duplicates if you have already used a query to filter records in a database.

  4. Rajiv

    How can i install ms access in windows 7?
    Please help me….
    Thanks in advance!!!!!

  5. Sandy

    This is a great feature BUT is there a way to find “fuzzy” duplicates not exact in more than one field (i.e. last name (Van Dyke; Vandyke), and address (49 Taft St; 49 Taft Street)?

Enter Your Email Here to Get Access for Free:

Go check your email!