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.

Read Also:  How to Update Access Database from Ms Excel

 

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.

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.

 

Note:

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



Access Database Tags: #microsoft access delete duplicate records from a table #microsoft access hide duplicates in a report #microsoft access remove duplicates combobox #ms access combobox remove duplicates #ms access criteria remove duplicates #ms access query criteria remove duplicates #ms access remove duplicates #ms access remove duplicates from a table #ms access remove duplicates in a query

Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in
MS Access Left Function in SQL Query and VBA Code
MS Access Left Function in SQL Query and VBA Code
Ms Access Left Join function or sometimes


Must read×

Top