Microsoft KB Archive/98230

{|
 * width="100%"|

ACC1x: How to Query for Duplicate Records

 * }

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.

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; </li> 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.</li></ol>

Keywords : kbusage

Issue type : kbhowto

Technology :