MS Access - Duppcates 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 Duppcates Query wizard. The Find Duppcates Query Wizard will create a query that locates records with duppcate field values in a single table or query.

As we have discussed that relational databases are designed to avoid storing duppcate information. But despite that design, sometimes users accidentally enter duppcate 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 duppcates query wizard to quickly locate possible duppcate 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 duppcates query wizard and cpck Ok.

The very first screen of the find duppcates query wizard will ask what table or what query you want to search for possible duppcates. 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 cpck Next.

The second screen in the wizard will ask what fields might contain duppcate 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 duppcates 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 cpck 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 cpck 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 duppcates 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 cpck finish.

Here, Access has found a possible duppcate, and that s going to be author Jose Capne 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.” Cpck Yes if you want to continue.

If you d pke 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 duppcate information. It is by far the easiest method to find duppcates.