As the administrator of a Microsoft Access database, one of the jobs that you may need to do is the keep the database working as efficiently as possible. A major cause of performance issues and database slowdown is erroneous or duplicated data contained in a table. We can keep these problems to a minimum by validating data entry and preventing duplicate information from being entered.
We may have inherited a database that already contains duplicate information, and in the case we can use the Find Duplicates Query Wizard to search out this data.
Using this wizard we can create a query that reports which record(s) in a table are duplicated using some field or fields in this table as a basis for the query. Microsoft Access asks which fields that you want to use for checking duplication and also which other fields you would like to appear in the query results. We can also use the Find Duplicates Query Wizard to help find possible duplicate key violations; a valuable trick when you want to take an existing table of data and make a unique field with one of the existing data fields.
Creating a Find Duplicates Query
From the Queries tab in the database window click on the New button
Choose the Find Duplicates Query wizard and click OK
Select the table or query that you want to search for duplicates and click Next
Select the field(s) that might contain the duplicate entries and click Next
Select any additional fields that you want to appear in the query results, click Next
Name the query and click Finish to complete.
If we look at the following example, we can see how this works by checking a table for duplicate values. Here we will check a contacts table, that lists Company Contact Details:
From the New Query list, select the Find Duplicates Query Wizard
Choosing the Find Duplicates Query Wizard from the New Query dialog box
From the list of tables/queries choose the table which may contain the duplicate values.
From the Available fields list, select the Company Name field, and move this to the Duplicate Value Fields list. Click Next to move on
Which field contains the Duplicate Values?
Which field contains the Duplicate Values?
If you need to show other fields in the query results, select these in the next screen before moving on. In our example, we have included the CustomerID field
Include Additional Query fields to display in your results
Include Additional Query fields to display in your results
Name the query and click Finish to complete the wizard. The query will now run and display the results, if there are any duplicates contained within the table:
Query results displaying any duplicate records.
As you can see above, the table contains duplicated values for the Company Name field, but unique CustomerID values (which is the Primary Key of the table).
Once you have verified the results, you can modify or delete the duplicated values contained in the table.
You may want to check out the following product, which overcomes some of the shortfalls in the Microsoft Access Find Duplicates Query Wizard: