Finding Duplicate Records in Access 2013
Synopsis: A common problem when working with databases is the presence of duplicate records. In this article we look at some of the facilities in Access 2013 for identifying duplicate records.
There are various causes for duplicate records in a database. One common scenario occurs when two or more databases are merged. Another situation that often happens is that a user adds a record to the database without realizing that that record already exists in the database.
One of the reasons that duplicate records happen so often is that it is not always easy to see that two records actually represent the same entity. For example, in this table from my movie database there are two entries for a movie called True Grit. But are these actually duplicates?
They are not. Although they have the same title, we can see from their year of release that they are not the same movie. So, the year of release will give us information about whether two records in this table are for the same movie.
However, note that there is another difference, and that is that one is marked as “Seen” and the other is not. The value of this field does not help us to identify duplicates because it is not an inherent property of the movie.
Do you need to learn Access 2013? Get my 9-hour of Microsoft Access 2013 training – click here.
So, when looking for duplicates it’s important to identify which fields to use, and which fields are not relevant.
Let’s look at one way of finding duplicate records. We’re going to use the tblMovie table in the database I’m working on. Select the Create tab then Query Wizard in the Queries group then Find Duplicates Query Wizard. Click OK.
The first screen of the Find Duplicates Query Wizard appears. Here we can choose to list tables, queries or both and to select the table or query we want to search for duplicates. In this case we select the table tblMovie and click Next.
On the next screen we specify which fields need to be equal to indicate a duplicate. On this occasion let’s say that a duplicate is indicated when two movies have the same Title and the same Country. Select each of those fields in the list on the left then click the single right-pointing arrow for them to be moved to the list on the right as shown here.
We know that this is not correct as we should be checking for the Year of Release to be the same as well, but let’s see what happens. Click Next.
On the next screen we specify whether to show any other fields in the event that Access 2013 finds any duplicates. In this case let’s specify that we want to show the Year of Release.
Click Next.
On this final wizard screen we enter a name for the query and decide whether to view the results or do more work on the design.
Click Finish.
The results show that Access 2013 has found two potential duplicates. Of course, if we had made Year of Release one of the fields to match it would have found none!
If we really had identified one or more duplicates, we might choose to delete them as an additional step.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
