MS Access - Duplicates Query Wizard

In this tutorial, we have created a variety of select queries, mainly from the Design View. But in Access, we can make use of some special query wizards to create a couple of specific select queries. The first one is Find Duplicates Query wizard. The Find Duplicates Query Wizard will create a query that locates records with duplicate field values in a single table or query.

As we have discussed that relational databases are designed to avoid storing duplicate information. But despite that design, sometimes users accidentally enter duplicate information.

Example

In a customer's table, you can have the same customer accidentally added twice. In such cases, the customer will have the same address, but different customer IDs, which can create problems with reporting. In this situation, you can make use of the duplicates query wizard to quickly locate possible duplicate entries.

Let us now open our Access database which contains tblAuthers table and go the Create tab and, in the queries group, select query wizard.

If you see the following screenshot, you have four different wizards to choose from. Select the find duplicates query wizard and click Ok.

The very first screen of the find duplicates query wizard will ask what table or what query you want to search for possible duplicates. Let us say we want to check our author's table to make sure that the same author hasn't accidentally been entered twice. So, select tblAuthors and click Next.

• The second screen in the wizard will ask what fields might contain duplicate information.

• Typically, you will not be using your primary key field, because, again, when you designate a field in Access as a primary key, Access will not allow duplicates to be entered.

• We will look at any other field or a combination of fields.

We can search by the last name or the first name and the last name, or you can search by their street address, or to be more specific with their telephone number or birthday.

Let us now search by the first name, the last name, and birthday and click Next.

The following screen in this wizard will ask for the fields we want to be displayed in our query. For this, hit the double arrow, all of the fields will move over to the additional query fields area, and will be added to our query results.

Let us now click Next. It will take us to the last screen in this query wizard.

In the following screen, enter how do you want to name your query.

By default, it's going to name it find duplicates for plus whatever the name of the object that you're querying. In this case, tblAuthors, but you can give it any other name too and click finish.

Here, Access has found a possible duplicate, and that's going to be author Jose Caline which has same birthday, same address, same telephone number but different AuthorIDs.

This one has definitely been entered twice by accident. We have now added all of the fields to our query, we could just go and delete the record. We also have to make sure that we don't have any related records in another table.

Select any record and choose Delete as in the following dialog box.

Access gives you a prompt, “You are about to delete one record.” Click Yes if you want to continue.

If you'd like to see how that wizard has created this query, go into the Design View and see what all has been added to this query.

As you can see in the above screenshot, we have our fields and some specific criteria underneath the first name field.

This is how this wizard is looking for that duplicate information. It is by far the easiest method to find duplicates.