Fri. Jun 14th, 2024
Ms Access Remove Duplicates using Query or Ms Excel
Ms Access Remove Duplicates using Query or Ms Excel

This tutorial will guide you to understand about Ms Access Remove Duplicates. Sometimes, you may find that duplicated data is totally annoying, and may cause a fault in your reports. There are 2 ways in handle this.


Using Ms Excel

1. Select all the records in the database. Then press Ctrl + C or copy them.

2. Open Ms. Excel. Paste the records in the database.

3. Select the records. Then go to Data > remove duplicates. Select which columns that should be contains duplicate, then click OK.

4. After the duplicates in Excel removed, select and copy the new records.

5. Go back to the Access database. Select the records in the table and paste back the new, no duplicate records.

Using Query

1. In Ms Access, go to Create tab > Query Wizard.

2. In the dialog box, select Find Duplicates Query Wizard, and then click OK.

3. In the table list, select the tables or database to be edited.


4. Next, select the field that may contains duplicate data. Hit next

5. Next, select the additional field that not listed as duplicate, but will appear in the query.


Example in Query

In the step 4, I choose MemberName, MemberLastName, and MemberStutus, but not MemberID. This is because MemberID is the primary key that contains different value, so even through the 3 fields is duplicated, Access will consider it as different data. Because of this, I enter the MemberID in additional field list instead in step 5.

Read Also:  MS Access Export To CSV

6. Name the query and hit finish to run the query. The duplicated records will appear.

7. Lastly, you can choose to edit the value or delete the entire records.



1. Always back up your database before you doing any changes. Once you update or change your database, it is non undo-able operation and you cannot revert it back.

2. In some cases, you may have the records that the fields have same values, but still considered as different records (example: 2 member have same name, last name, and have same member status). For this records, you have to create a new field column to differ them.

There still more ways to remove duplicate data aside the 2 methods above. Good luck in removing your duplicate data