Pin Me

Finding and Eliminating Duplicate Records in an Access 2007 Database

written by: John Garger•edited by: Michele McDonough•updated: 9/25/2010

Large databases can quickly become unwieldy as more and more records are added. Learn how to easily find and eliminate duplicate records in a large Access 2007 database.

  • slide 1 of 4

    Access 2007 is the backbone of many information systems for small and medium-sized businesses. What usually start out as small databases grow quickly to large behemoths that can be difficult to manage. Often, duplicate records in an Access 2007 database cause problems that can cost a company real dollars because the database becomes bloated thereby reducing efficiency. Read on to learn how to find and eliminate duplicate records in a large Access 2007 database to keep your data clean and efficient.

  • slide 2 of 4

    What are Duplicate Records in an Access 2007 Database?

    It is likely that at least some of the data in your Access 2007 database is a duplicate of other data. However, this is not always a bad thing. In a products database, it is likely that at least some of the products share the same price and in an address database it is likely that at least some of the people live in the same city or have the same zip code.

    Duplicate records occur only when the entire record is repeated more than once. Duplicate records are quite common for several reasons. First, if more than one person enters records into the database, they may be working from the same list of entries and each enter the same record. Data imported from other sources may have duplicate records even if the duplicate record comes from two different importations.

    Duplicate records cost companies money because, generally, larger databases are more difficult to manage than smaller ones. Also, customer databases with duplicate records may mean that your company is sending out more than one advertisement to the same customer. If the same product is in a database more than once, what happens when the price of the product is updated for just one record? Which price is correct? Read on to learn how to find and eliminate duplicate records in an Access 2007 database.

  • slide 3 of 4

    Finding and Eliminating Duplicate Records in an Access 2007 Database

    Figure 1 - Click the Access 2007 Query Wizard Button 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.

    Figure 2 - Access 2007 Find Duplicate Query Wizard 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.

    Figure 3 - Choose the Fields for the Access 2007 Find Duplicate Query Wizard 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.

    Figure 4 - Choose the Access 2007 Fields That Will Help You Identify Duplicate Records 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.

    Figure 5 - Name Your Access 2007 Query 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.

  • slide 4 of 4

    Conclusion

    Access 2007 has a built-in query wizard to help you find and fix duplicate records in a table. This wizard can be quite powerful since it not only helps you identify the duplicate records, it allows you to include additional fields so that you can find exactly where the duplicate records reside in your data table. This wizard is invaluable for large Access 2007 databases where scrolling through record after record looking for duplicates is time consuming or prohibitive because of the size of the database. In addition, this FIND DUPLICATE QUERY WIZARD is far superior to using the find and replace function in Access 2007. That method should be reserved only for small Access 2007 databases.