How to Remove Duplicates in Access 2013/2016/2019?

Duplicate data often arises when multiple users enter data into the database simultaneously or if the database was not designed to check for duplicates. The easiest way to remove duplicate access records is by making use of the query. This post describes how to remove duplicates in Access with ease.

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

What Is A Duplicate Data?

Data is called duplicate data when it is an exact copy of another dataset or gives the same information as the other dataset.

Duplicate data occurs at different levels:

1. More than two records containing duplicate field: Records can be considered duplicates when some of the fields have matching values. For Example, if two fields are entered with the same Customer ID, then it will be considered as a duplicate record.
2. More than two tables containing similar data: In some cases, say, there are two tables, one of Customer and the 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 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 Is Not Considered 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 Steve Smith, but actually they all are different people. As they will have different Customer IDs, addresses, etc.
2. Data returned in a Query resembles having 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.

Also Read: How To Unlock Or Remove Access Database LDB/LACCDB File?

How To Resolve And Prevent Duplicacy Of Data In Access?

Data duplication 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 overlap. 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 to the other table.
5. Each and every table should have a Primary key.
6. If fields do not have a Primary Key, then they should have unique indexes, unique values for the fields.
7. To find existing values easily by using a list box and creating a data input.

How To Delete Duplicate Records In Access?

Here are 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 on 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 columns.
9. At last, on the Design tab, click Run>Yes.

How to Recover Deleted Records From MS Access Database?

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

Feeling frustrated, well, no need for that …!

You can easily delete records in Access by using the Access Repair and Recovery Tool. This software is used to recover damaged, corrupt, or inaccessible databases. 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 forms, reports, tables, and queries.

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

access-repair-main-screen

access-select-database

access-repairing

access-repairing-completed

access-preview

access-save-location

access-saving

Precautions To Be  Followed Before Deleting Duplicate Data

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

Frequently Asked Questions:

Is There a Formula to Remove Duplicates?

Yes, you can use the UNIQUE() function in MS Access or similar formulas to show unique values in real time as data changes.

Why Remove Duplicates?

Duplicate records can impact the quality of your database data, bias prediction results, and waste storage space. Removing such duplicates confirms that each value is unique and correctly represents your data.

Which Command Is Used to Remove Duplicate Records?

By using the DISTINCT command, you can delete duplicate records. However, you can select only unique rows & then insert them back into the original table, efficiently deleting duplicates.

What Is the Shortcut Key for Duplicates?

After selecting CTRL+A, you can use the shortcut keys in this sequence: Alt + H + L + H + D.

Final Verdict

Duplicate records slow down performance and impact data quality. With Access tools like queries, indexing, and VBA, managing them becomes easy. Stay organized, and your database will remain efficient and accurate.

Moreover, if you find any difficulty meanwhile this process of removing duplicates Access records process then share your problem with us on our Facebook and Twitter social accounts.

I hope you liked this post!

Still having issues? Fix them with this Access repair tool:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

1. Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
2. Click Browse and Search option to locate corrupt Access database.
3. Click Repair button to repair & preview the database objects.

Pearson Willey

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.