Provided by Allen Browne, firstname.lastname@example.org
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).
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|