Find and Delete (all but 1) Duplicate Records from Table with VBA

[Heatshiver]

I want to find duplicate records of one field in a table and delete all but one of the records using VBA.

I was thinking of using: CurrentDb.Execute "DELETE *
but I'm not sure how to put what I want after that.

I did use the Query Wizard and was able to get the duplicate SQL needed:

SELECT First(tblUserID.[UserID]) AS [UserID Field], Count(tblUserID.[UserID]) AS NumberOfDups
FROM tblUserID
GROUP BY tblUserID.[UserID]
HAVING (((Count(tblUserID.[UserID]))>1));

Any help would be much appreciated! Thanks.

 

[John Big Booty]

A good way to start is by creating a select query that selects the records you are interested in deleting. Once you are sure it is selecting only the records you wish to delete convert it to a delete query.

 

[Heatshiver]

That was my thought, but I wasn't sure how to make the SQL in VBA to just delete all but one.

My solution, for now, is to change the Index to Yes (No Duplicates), and on the only form that uses the UserID set the Error event to: Response = acDataErrContinue

Likely not the best method, but it works without issue.

 

[John Big Booty]

If you construct and experiment with a query using the query builder, once you have it running to your satisfaction. You can them use the SQL view option to view the query as raw SQL, which you can then copy and paste into your code.

 

[Heatshiver]

The SQL I posted was actually from the Query Wizard, but it only found the duplicates.

What can I put in the Criteria to delete all but one duplicate?

Thanks.

 

[John Big Booty]

Do your records each have a unique ID or some other way that they may be differentiated?

 

[Heatshiver]

Each has a different PK# associated to each record.

 

[John Big Booty]

What I would probably do is first build a Totals Query to select, say, the Max of the ID, and then use that in a Find Unmatched query and delete all the records that do not match the results of the first query.

If this is not going to be a one off, then it strongly suggests that you may have a problem with your table structure and data validation, if there is an on going need to keep deleting duplicates.

 

[Heatshiver]

I will try your suggestion as soon as I can.

The structure of this database makes this table a bit odd. Technically there is one main table linked to several others. The main table records are sought out by date, but also record the UserID for the sake of seeing who input the data.

I needed a way to create UserIDs so that the admins using it could add new people when necessary. If I added through the main table it would associate the UserID to a date with no other data. This could potentially create a problem for a user if they started entering input on that date, as they can only use a specific date once. Also, it would just be an empty unnecessary record that would be shown in the combobox of certain forms that displays previous days of work for that user. So I added a separate table for UserID to ensure there would be no date association. Unfortunately, Access forced me to make a relationship to the main table in order for the UserID to be recorded into the main table's data as I use an invisible form to populate the UserID from the UserID table of any form (using the main table) requiring it.