In Microsoft Access, you can create a query to find duplicate entries in a table. This query will return all the duplicate entries in the table, as well as the information about which table each entry is from. To create a query that finds duplicate entries in a table, follow these steps:

  1. In Microsoft Access, open the Tables window.
  2. In the Tables window, click on thetab at the top of the window.
  3. In the Query window, enter a query into the text box at the bottom of the window.
  4. Click on OK to close the Query window.

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.