Finding duplicate records in a table

        9 votes: *****     13,834 views      No comments
by Allen Browne, 20 April 2005    (for ALL VERSIONS of Access)

Microsoft Access Tips for xBase developers

Provided by Allen Browne,

Finding Duplicates in an imported DBF

Importing a DBF file into Access is no problem, but if the DBF has been in service for any period, you might discover duplicates in the field which is supposed to be the primary key for referential lookups. If any record has a duplicate in this field, it can be very frustrating trying to make the field a Primary Key in Access, especially if there are thousands of records to search through. So how do you locate the duplicates?

If you know SQL or stumble across the Find Duplicates Query Wizard, the solution may be obvious. This Wizard actually uses different methods for different needs. The discussion here covers only one method, and works with version 1 (which doesn't have the Wizard).

  1. Create a new query, and add the table you are working on.
  2. Click the Totals button on the toolbar (upper case Sigma). A "Total" row will be added to the grid.
  3. Drag your field into the grid, and in the Total row, select Group By.
  4. Drag the same field into the grid a second time. For this second instance, select Count for the Total row, and enter ">1" in the Criteria row.
  5. Run the query, to view the contents of offending records, and the number of duplicates you need to locate.

Query screenshot

You may be interested to see what Access generates behind the scenes. If the table is called "Customers" and the field is "CustomerID", the SQL string will be something like:

  SELECT DISTINCTROW CustomerID, Count(CustomerID) AS CountOfCustomerID
  FROM Customers
  GROUP BY CustomerID
  HAVING (((Count(CustomerID))>1));

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Finding duplicate records in a table'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).