Microsoft Access Tips for xBase developers

Provided by Allen Browne, 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.

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));