MS Access —

Finding Duplicate Records

The helps you find records that have the same value in one or more fields. So when would you need to use a Find Duplicates Query?

Step One: Select the table or query in which you want to search for values.

Step Two: Select the field or fields that contain the duplicate values.

The query displays those records that have duplicate values in both the LastName and FirstName fields.

Here are a few scenarios:

  • To search for duplicate values in an Orders table to find out which customers have placed more than one order.

  • To search for duplicate values in several fields to search for data-entry errors. For example, if you and another user accidentally entered the same customers into a table, you could search for duplicate values in the LastName and FirstName fields to find and delete the duplicated records.

Access provides a wizard to make creating a query that finds duplicate information in a snap.

"Arrr-rrgh!" you shout as you realize that you and another co-worker have just entered the same customers into a database. Fortunately, you can find the duplicated records by using the Find Duplicates Query Wizard.

  1. If necessary, open the Lesson 6 database.

    Now let's create a new query.

  2. In the Database window, click the Queries icon in the Objects bar and click the New button.

    The New Query dialog box appears.

  3. Select the Find Duplicates Query Wizard and click OK.

    The first step of the Find Duplicates Query Wizard appears, as shown in figure. Choose the table or query that you want to sift through for .

  4. Select the tblCustomers table and click Next.

    The second step of the Find Duplicates Query Wizard appears, as shown in figure. Her you will tell Access which field or fields might contain the duplicate information.

    Since you are trying to find duplicate customers, you decide to search the FirstName and LastName fields for duplicate values.

  5. Double-click the LastName and FirstName fields.

    The LastName and FirstName fields appear in the "Duplicate-value fields" list.

  6. Click Next.

    The next step of the appears. You can select any field (other than the ones you specified in Step 4) that you want to be displayed in the query.

    You decide to display the City and State fields so you can verify that the records are duplicates.

  7. Double-click the City and State fields. Click Next when you're finished.

    You're finished! Well, almost. You have to give your query a name, or you can accept the default name Access gives the query.

  8. Click Finish.

    Access saves the query with the name "Find duplicates for tblCustomers" and displays the results of the query, as shown in figure.

  9. Close the query.

To use the find duplicates query wizard:

  1. in the database window, click the queries icon in the objects bar and click the new button.

  2. select find duplicates query wizard and click ok.

  3. select the table or query you want to search and click next.

  4. double-click the field(s) that may contain the duplicate values and click next.

  5. double-click any additional fields that you want to appear in the query results and click next.

  6. click finish.