How to Create a Query to Quickly Trace Duplicate Records in MS Access

The following article will explain the ways to create a Query for tracing Duplicate records in MS Access.

While working on databases in MS Access, users often face problems with Duplicate data, or data getting copied several times, and it can question the workability and efficiency of your database. Presence of duplicate data or outdated data can cause several unexpected problems to its users so it’s best to avoid these duplicates. MS Access provides us with options and queries through which we can search and eliminate the chances of data duplicity within tables and forms.Creating A Query To Trace Duplicates In Quick Time In MS Access

Understanding the Duplicate Data

It’s essential to understand which record is a duplicate record and which is not. Duplicate records are basically those records which refer to the same thing or person as a separate record. However, we can’t label each and every similar information or data as duplicate record.

For example, if we have records of two or three orders but their registered dates are different, we can’t call them Duplicate records. Removing them would be a critical data loss. But what if we have multiple records of the same person but with different shipping addresses? Then we can figure out that the older or out of date address is actually a duplicate record and should be eliminated from the database.

That’s why it’s important to deal with duplicate records because if they aren’t traced or eliminated, the company, in above example, could end up sending products to the wrong addresses.

How to make a Tracing Query for Duplicate Record

MS Access provides the option of tracing all the duplicate records by making a Query and once they are traced, users can delete them or deal with them accordingly.

Here are the steps to follow for creating a query to trace duplicates:

  1. Select the Query Wizard from Queries Section. Users can locate Queries section from the Create tab in Access.
  2. Find Duplicates Query WizardIn the New Query Dialog-box, select the option of ‘Find Duplicates Query wizard’ and click OK.
  3. You can run the query for any table/s, so select the table from the menu and click Next. Select the relevant fields from the Available fields Menu and click on the right arrow ‘>’ button to add them in Duplicate-value fields and then click Next. Don’t select those fields which can be identical in non-duplicate records. Say you are running a query for customers table; in that case you can select First and Last name fields.
  4. The Duplicate Query Wizard will then ask you to select Additional fields that can be viewed in Query results. It will help you in distinguishing the duplicate records. Try to add such fields which relate to the location of Customers. You can also add Phone Number or Email ID field to get clear results.
  5. As you click Next, Access will show you final dialogue box asking you to enter a name for your Query. It however provides a default one. Click on the finish button to run the Query.
  6. Access will go through the document and would present you with all the duplicate records, if there are any, for review and modification. You can then delete the outdated and incorrect records.

Professional users of MS Access have often felt the need of absolute data safety while working on their preferred database. The Compact and Repair feature present in MS Access does not offer holistic recovery capacity. Hence a state of the art Access recovery tool is mandated for businesses running MS Access.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server fix and excel recovery software products. For more information visit

Comments are closed.