Finding first line of duplicates for deletion

[Tommy B]

Hi Guys,

Hope you can help me out

What I am trying to do is identify duplicate records and delete them from my master table.

I used the wizard to create a duplicates query, it returns the two duplicate records side by side (in actual jfact about 45,000 records side by side!!!)Now what I really need it to do is return just one of the duplicate records as I need to keep one, just removing the duplicate...if that makes sense.

If there were less dupes i could do this manually with my current query by just selecting and removing the offending records...however i am dealing with thousands of them!!!

Is there a way I can get the dupes query to look for dupes and return just one of the records so I can bulk delete them without removing the original record and the duplicate?

Hope this makes sense!!!

Any help is much appreciated

Tommy B

 

[Tommy B]

Excellent! This method works perfectly

Thanks for the help Pat!

Tom.

 

[Rich_Lovina]

Pat Hartman wrote...

Hi there,

Pat wrote: "...The easiest way to do this if you don't care which record is saved is..."

Now, in my dbase I do care, since I have the data set such that the oldest record (by date or older autonumber) appears first in the duplicate set. How do I write the sql for DELETE only the older date (or lower autonumber)?

Or is there a VB solution to the same problem?
Thanks in advance any viewers.

 

[Rich_Lovina]

Thanks Pat, that was done for manual deletions.