How to use a FIND DUPLICATES query to delete one of the records?

[gojets1721]

I have a Find Duplicates query that identified over 2000 duplicates (don't ask why).

How do I go about deleting one of the duplicates but keep the other? I found a few articles that say create a temp table, but none are really too specific on instructions so I have no idea how to do that or if its even correct.

Any advice is greatly appreciated. Thanks

 

[theDBguy]

Does your table have an ID field?

 

[isladogs]

Please read the first two items listed in similar threads at the bottom of this page.
These provide several possible approaches that you can use.
Choose whichever you like / understand. All work.

 

[gojets1721]

Yes it has the Access auto-generated ID field. They also have a user-generated # field. That's how I identified the duplicates in the query. Just not sure now how to use this query to delete one of the entries. The similar posts below venture into VBA and I'd prefer not to use that method if possible.

 

[theDBguy]

Yes it has the Access auto-generated ID field. They also have a user-generated # field. That's how I identified the duplicates in the query. Just not sure now how to use this query to delete one of the entries. The similar posts below venture into VBA and I'd prefer not to use that method if possible.

Hi. This is just a guess, since I can't see what you got:

DELETE T1.*
FROM TableName T1
WHERE [ID] Not In(SELECT Min(T2.ID)
FROM TableName T2
GROUP BY T2.[UserGeneratedField])

(untested)
Hope that helps...


PS. Make sure you have a backup copy before trying the above.

 

[isladogs]

Yes it has the Access auto-generated ID field. They also have a user-generated # field. That's how I identified the duplicates in the query. Just not sure now how to use this query to delete one of the entries. The similar posts below venture into VBA and I'd prefer not to use that method if possible.

Some methods involve vba but not all. For example this quote is from one of those threads.

The simplest solution for eliminating duplicates is:
1. Create a new empty table.
2. Add the necessary unique indexes to PREVENT duplicates.
3. Create an append query to select the old data and append it to the new table. Sort this query to get the record you want to save to sort first.

At the end of the append, you will get an error message telling you how many duplicates were ignored.