How To Find And Remove Duplicate MS Access Records In Access 2013 & 2016

Synopsis: 

Is your Access 2007/2010/2013/2016 database frequently showing duplicate records issue? Well if it’s a yes, then what will you do to instantly figure out such duplicate records and delete them from your database. Don’t know such ways, well no need to worry just go through this post and you will get to know all the easy tricks to find and delete duplicate records in Access.

Duplicate data often arise when multiple users enter data to the database at the same time or if the database was not designed to check for duplicates.  Well, the easiest way to remove duplicate Access records is by making use of the query.

So, to accomplish Access remove duplicates task the very first thing you need to do is remove duplicates Access records with a query in your database.

Let’s know how it is to be done…!

What Is A duplicate data?

A data is called duplicate data when it is the exact copy of another data or gives the same thing of the other data.

Duplicate data occurs at different levels:

1. More than two records containing duplicate field: Records can be considered as duplicates when some of the fields records having matching values. For Example, if two fields are entered with the same Customer ID, then it will be considered as duplicate record.
2. More than two tables containing similar data: In some cases say, there are two tables one is of Customer and other of the client, both have records of the same customer, so duplicacy can be removed by merging the two tables into a single table.
3. More than two databases containing similar data: More than two databases containing similar data or the database is assumed to be overlapping the current database, and then comparing each database needs to be compared, which is not an easy task. But with the help of third-party vendors tools can be used to compare the structure and contents of the database.

Which Data Are Not Considered As Duplicate Data?

1. In some cases, more than two records show the same value but do not refer to the same thing. For Example, say many customers have the name as Steve Smith, but actually they all are different persons. As they will have different Customer Ids, addresses, etc.
2. Data returned in a Query, resembles to have duplicate records, because it does not specify the unique identity of each record. But by the same Query by primary key field, it will show records uniquely.

How To Find Duplicate Records In Access?

You can find the duplicate records using the Query Wizard, and the steps to do so are as follows:

1. On the Create tab, from the Queries group, click Query Wizard.
2. In the new query dialog box, tap to Find Duplicate Query Wizard option and then click Ok.

3. From the list of tables, choose the appropriate table of your use and then click to the Next

4. Select the fields you want to match with and then click to the Next

5. From the available field list, select for the field that contains the data that you want to update and click to Next

6. Accept the suggested query name or assign any name and then click to the Finish option to run the query.

How To Resolve And Prevent Duplicacy Of Data In Access?

Data duplicity in the table generally arises because of overlapping records. The following steps are required to be followed in order to remove duplicate Access records.

1. The query must show the records of the table that is overlapping. It can be done by sorting the records by field.
2. A distinct entity of a duplicate record can be edited.
3. If there is another record of the same entity, then duplicate records can be deleted.
4. It might also happen that one duplicate data is deleted and the other is edited. Then the deleted record from the table must be cascaded with the other table.
5. Each and every table should have a Primary key.
6. If fields do not have Primary Key, then it should have unique indexes, unique values for the fields.
7. To find existing values easily by using a list box and by creating data input.

How To Delete Duplicate Records In Access?

Here is the complete steps on how to create and run a delete query in Access 2013/2016.

1. Go to the query designing group and click the create tab. Now in the show table dialog box, double click the table from which you want to delete your records.
2. Make a double-tap to the asterisks (*) to add all the table fields to the query designer.
3. Simply add the fields that you have kept to identify the records for deletion.
4. You can also put criteria in the Criteria Row.
5. Clear the Show checkbox for each criteria field.
6. From the Design tab click Run. Verify that the query returns the records that you want to delete.
7. From the Design tab click the Design tab and then the Delete Access will change the select query to a delete query, hides the Show row in the lower section of the design grid, and adds the Delete row.
8. One more verification required that is before you run the delete query, make sure that the delete row in the *(all fields) column displays From and Where must appear in any of the criteria column.
9. At last, on the Design tab, click Run>Yes.

How to Recover Deleted Records From MS Access Database?

Sometime situation also happens that while deleting the Access Duplicate Records, accidentally both the actual and duplicate file is gets deleted. Then at times what will be your step. How can you get it back?

Feeling frustrated, well no need of that …!

As you can easily make deleted records recovery in Access by using Access Repair And Recovery Tool. This software is used to recover damaged, corrupt or inaccessible database. It easily recovers the database from any sort of corruption. It can restore corrupt relations, queries, tables, and indexes.

* By clicking the Download button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

This tool can even recover password-protected modules or forms. It’s advantageous for small, mid-size enterprises and for individual businesses to run and maintain their database. This tool helps in building form, reports, tables, and queries.

Step By Step Guide On How To Retrieve Deleted Records In Microsoft Access

Precautions To Be  Followed Before Deleting Duplicate Data

1. Database file should not be read only.
2. Only one person should work on the database at a time, this reduces data conflict.
3. One must open the database in exclusive mode by clicking the File tab, then Open, click the arrow next to Open button and select Open Exclusive.
4. The database must be backed up before deleting records.

Wrap up:

After reading the complete post on how to remove duplicates in Access. Now it’s become too easy for you to perform Access remove duplicates task. But if you find any difficulty meanwhile this remove duplicates Access records process then share your problem with us in our FB and Twitter social account.