Using the Find Duplicates Query Wizard

In spite of our best efforts to store data efficiently in databases, errors happen that result in duplication of information. Sometimes data—which may or may not be accurate—is imported from other sources. Some departments may be using databases that were poorly designed years ago, but users have continued to enter data into them anyway. And, of course, humans make data entry errors. When it is time to clean up the data, the Find Duplicates Query Wizard can help with that task.

Finding Duplicate Records

In our database, tblStudents holds some records that have been entered over time and came from multiple sources. The possibility exists that some students may have been accidentally entered multiple times. If the primary key is entered incorrectly and doesn't match a primary key already in the table, then Access won't prevent that data from being entered. This is true of data we append from other sources or data directly entered into the database.

We will use the Find Duplicates Query Wizard to search for duplicate records in
tblStudents.

Step1. To activate the Create tab, on the Ribbon,

Click the Create tab

Step2. To begin building the Find Duplicates query, in the Queries group,

Click

Step3. To indicate that we want to create a Find Duplicates query,

Double-Click Find Duplicates Query Wizard

The first step of the wizard opens. The wizard asks for the name of the table we need to search for duplicates.

Step4. To choose the table for our search,

scroll down and Click Table: tblStudents

NOTE: We could view names for queries or for tables and queries by clicking the appropriate radio button.

Step5. To move to the next step in the wizard,

Click

The next window is asking for the fields that will indicate duplication. In other words, what would make two records look suspiciously similar? Two records with the same first name and last name, while not conclusive, would warrant a closer look. Let's look for records with matching first and last names.

Step6. To select the fields with possible duplicated information,

Double-Click last_name, Double-Click first_name

Step7. To move to the next step of the wizard,

Click

This window asks for other fields that should display in the result set. If the query finds two students named Jim Jones, how will we know if they are the same or different individuals? For our exercise today, we will choose to see the email, local_phone, and local_address fields. Those three fields will give a check of further similarity.

Step8. To choose other fields for the query display,

Double-Click email, local_phone, and local_address

Step9. To move to the next step of the wizard,

Click

This last step of the wizard asks for a query name.

Step10. To name the query and complete the wizard, in the Name field, type:

qryStudentsFindDuplicateNames Enter key

The Find Duplicates query result set includes 28 instances of students with the same name. Much of the information for emails, phone numbers, and addresses is different between the records. This may be because a student has moved but instead their record being updated, a new record was created for a student. Also some of these names might just be two students who share the same name. Notice that the two Brandon Diaz records contain information that is almost identical, except for the email address. The email addresses are similar enough to suggest that a data entry error may have cause this duplication. This information provides a starting point for researching and correcting the data problems. We will not delete any data at this point because it is unclear which records are incorrect; however, if we know which records were false records, we could delete them from this query.

Step11. Close the query.

Now let's look at the wizard for finding unmatched records.