How to use a FIND DUPLICATES query to delete one of the records? (1 Viewer)

gojets1721

Registered User.
Local time
Yesterday, 23:51
Joined
Jun 11, 2019
Messages
429
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

I’m here to help
Staff member
Local time
Yesterday, 23:51
Joined
Oct 29, 2018
Messages
21,358
Does your table have an ID field?
 

isladogs

MVP / VIP
Local time
Today, 06:51
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Yesterday, 23:51
Joined
Jun 11, 2019
Messages
429
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

I’m here to help
Staff member
Local time
Yesterday, 23:51
Joined
Oct 29, 2018
Messages
21,358
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:
Code:
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

MVP / VIP
Local time
Today, 06:51
Joined
Jan 14, 2017
Messages
18,186
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.
 

Users who are viewing this thread

Top Bottom