Delete Duplicates from Find Duplicates Query

[bethabernathy]

Hi - Is there a way to delete the Duplicates from a Find Duplicates Query that displays all the Duplicates? What syntax would you use?

Thanks, Beth beth@integratedresourcemgmt.com

 

[petersdaniel]

DELETE * FROM YourTable WHERE EXISTS (your Find Dupe SQL Here);
petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmm,

Hopefully, you did NOT rush right in and do the above. It will delete ALL of the "Duplicates", leaving behind NO record.

In general, the "De-Dup" process goal, is to remove the "extra" copies. Not ALL of them. For that, you need some additional processing. It CAN be done in pure SQL, but I prefer a VB(A) procedure which loops through the Dup recordset. For each "KeyValue", it finds the first matching rec in the source, and then enters a loop which finds and deleted the REMAINDER of the matching records.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[nickjar2]

Michael,

I always use VBA to loop through the dupe query, and delete all but ONE of the dupes.

U said it could be done using just SQL, how is that done? I assumed the only way was to use code.
Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)