tech on the net

MS Access: Use a query to find duplicates in Access 2003/XP/2000/97

See solution in other versions of Access:

Question: In Microsoft Access 2003/XP/2000/97, I want to use a query to find duplicates in a table. How do I do this?

Answer: Create a new query and then select the "Find Duplicates Query Wizard" option in the New Query window. This should walk you through the setup of your query.

Below is an example to demonstrate how to use the "Find Duplicates Query Wizard". In our example, we have a table called suppliers. We're looking for suppliers that have the same name.

In your database window, select Queries and then click on the New button.

Microsoft Access

When the "New Query" window appears, select "Find Duplicates Query Wizard" and click on the OK button.

Microsoft Access

Select the table that wish to search for duplicates. In this example, we are searching the Suppliers table. Click on the Next button.

Microsoft Access

Select the field(s) that might contain duplicates. In this case, we are looking for suppliers with the same CompanyName. So we selected the CompanyName field and clicked on the right arrow to send the CompanyName to the right box on the screen. Click on the Next button.

Microsoft Access

Next, select the others fields that you would like to see in the query result set. In this case, we wanted to see the SupplierID, the ContactName, and the ContactTitle. Click on the Next button.

Microsoft Access

Next select the name of your query. In this example, we chose the name "Find duplicates for Suppliers". Then click on the Finish button.

Microsoft Access

Finally, the query results will display. In this case, we see two records that have a Company Name of "IBM". The additional fields (Supplier ID, Contact Name, and Contact Title) just help us identify the records.

Microsoft Access