ACC1x: How to Query for Duplicate Records

ID: Q98230

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

When you import data from other databases, records may contain duplicate information in the primary key fields. You can run a make-table query to eliminate the duplicate data, but if key fields for two or more records contain the same value, you may want to reconcile the information manually to make the records unique.

This article includes sample queries and shows how to list duplicate information for keys containing single fields or multiple fields.

MORE INFORMATION

Single-Field Keys

Note that this example assumes you have a table called MyTable, with the primary key on the Name field.

Open MyTable in Design view and index the Name field, as shown below:


      Table: MyTable
      -------------------------------
      Field: Name
         Indexed: Yes (Duplicates OK)

Create the following query based on MyTable:


      Query: Find Duplicates
      ----------------------
      Field: Name
         Total: Group By
      Field: Name
         Total: Count
         Criteria: >1

   The equivalent SQL statement is as follows:

      SELECT DISTINCTROW
         Name, Count(Name) As CountOfName
      FROM
         MyTable
      GROUP BY
         Name
      HAVING
         Count(Name)>1
      WITH OWNERACCESS OPTION;

Run the query. Duplicate key values are displayed in the first column and the number of times the value appears is displayed in the second column.

Multiple-Field Keys

This example assumes you have a table called MyTable, with a double primary key on the First Name and Last Name fields.

Open MyTable in Design view and index the First Name and Last Name fields, as shown below:


      Table: MyTable
      -------------------------------
      Field: First Name
         Indexed: Yes (Duplicates OK)
      Field: Last Name
         Indexed: Yes (Duplicates OK)

Create the following query based on MyTable:


      Query: Find Duplicates
      ----------------------
      Field: Last Name
         Total: Group By
      Field: First Name
         Total: Group By
      Field: First Name
         Total: Count
         Criteria: >1

   The equivalent SQL statement is as follows:

      SELECT DISTINCTROW
         [Last Name], [First Name],
         Count([First Name]) As [CountOfFirst Name]
      FROM
         MyTable
      GROUP BY
         [Last Name], [First Name]
      HAVING
         Count([First Name])>1
      WITH OWNERACCESS OPTION;

Run the query. Duplicate key values are displayed in the first two columns and the number of times the value appears is displayed in the third column.

Keywords : kbusage QryMktbl
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto