SEARCH

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.

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

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • 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)?

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!