Duplicate records

[Kojo Tay]

After finding duplicate records, how do I delete one
programatically?

 

[TC]

If they are *truly* duplicates - ie. identical in every respect - you will
not be able to do this with an SQL DELETE statement. You will have to use
VBA code to open a recordset, find the duplicates, & delete all but the
first (gfor example) of each set of duplicate records.

HTH,
TC

 

[Wayne Morgan]

The trick to deleting one is figuring out the parameters that make one of them better than
the other so that you know which one to delete. While tedious, I've usually found it
better to go though the duplicates manually.

If you really don't care which of the duplicate records is removed, you can run a delete
query with a parameter, such as filtering on the one with the highest ID number field (if
you use one).

 

[Wayne Morgan]

ps.

If you don't have an ID field, you can sort the records so that the dupes will be together
and add a running record count field to the table, number each record from 1 to however
many records. You now have something different about each record that will let you keep
one and delete the rest.

 

[cafe]

The trick to deleting one is figuring out the parameters that make one of them better than
the other so that you know which one to delete.

(snip)


But if they are truly duplicates - in every respect - there is no such
thing, by definition!

TC

 

[Wayne Morgan]

Agreed, but usually there is a unique Id field or similar, especially since Access offers
to make a Primary Key for you if you don't make one for yourself when you first save the
table.