remove duplicate access records

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 arises 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 is to use a query for deleting up the duplicate records. But first of all you need to find the duplicate records with a query in your database.

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 field record having matching values. For Example if two fields are entered with 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 client, both have records of 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 tool can be used to compare the structure and contents of the database.

Duplicate Record

Which data are not considered as duplicate data?

  1. In some cases more than two records show same value but do not refer 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, address 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.Duplicacy of records

How To Find Duplicate Records In Access?

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 duplicacy in the table is generally arises because of overlapping records. Following steps are required to be followed in order to remove the duplicacy.

  1. 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 same entity, then duplicate record can be deleted.
  4. It might also happen that one duplicate data is deleted and other is edited. Then 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 list box and by creating data input.

YOU MAY ALSO LIKE:

How To Fix Ms Access Error ‘3022’: “The Changes You Requested To The Table Were Not Successful “?

How To Delete Duplicate Records In Access?

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 check box 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 time 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. This tool easily recovers the database from any sort of corruption. It can restore corrupt relations, queries, tables and indexes. This tool can even recover password protected modules or forms. This tool has been advantageous for small, mid-size enterprises and for individual business 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 database in exclusive mode by clicking File tab, then Open, click the arrow next to Open button and select Open Exclusive.
  4. Database must be backed up before deleting records.
How To Find And Remove Duplicate MS Access Records In Access 2013 & 2016