Finding and Eliminating Duplicate Records in an Access 2007 Database
Luckily, Access 2007 has an easy-to-use, built-in tool for finding duplicate records in any size database. However, this tool is probably overkill for small database where using the find and replace function would be much quicker. For large databases, however, the FIND DUPLICATES QUERY WIZARD is the wiser choice.
To start finding duplicate records in your database, open the table where you suspect duplicate records reside. On the CREATE tab on the Access 2007 Ribbon, click on the QUERY WIZARD button (see Figure 1). The NEW QUERY window should now be open. From the list of available query wizards, choose the FIND DUPLICATES QUERY WIZARD and then click OK (see Figure 2). The FIND DUPLICATE QUERY WIZARD window should now be open.
In the FIND DUPLICATE QUERY WIZARD window, choose the table where you would like to search for duplicate records. Then, click the NEXT button. In this next window, choose the fields from left that you want to check for duplicate records. Simply click on the field in the left window and use the arrow buttons to add or remove one field at a time or use the double arrow buttons to add or remove all of the fields at a time (see Figure 3). When done selecting your fields, click NEXT.
This next window needs some explanation. Although you may only be searching one field for duplicates, you may still need the remaining fields to help you identify the actual records that are duplicates. Here, choose the fields from the left window that will help you identify the duplicate records (see Figure 4). Keep in mind that these fields won’t be checked for duplicates. They will only be provided to help you identify records. When you have chosen the fields you want, click NEXT.
This last window of the FIND DUPLICATE QUERY WIZARD asks whether you would like to name your query (see Figure 5). Access 2007 recommends a name based on the name of the table you are searching for duplicate records. You also have the option to VIEW THE RESULTS of your query or MODIFY THE DESIGN. For now, leave VIEW THE RESULTS radio button chosen and click on click on the FINISH button.
Immediately, a new table is created containing only duplicate records from the table and fields you chose to search for duplicate records. This new table will help you identify where you need to either eliminate or modify the duplicate records in the original table.