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.