Finding duplicate records in a table

        8 votes: *****      13,380 views      0 comments
by Allen Browne, 20 April 2005    (All versions of Access)

Microsoft Access Tips for xBase developers

Provided by Allen Browne, allen@allenbrowne.com


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: Poor Your rating: Not so good Your rating: Average Your rating: Good Your 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...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.