how to delete similar records in Access?

[Guest]

I have a table that has many similar but not duplicate records. What is the
same are the LastName, FirstName, and DOB fields. Other field values in these
records are different. How can I delete similar records based on repeating
LastaName, FirstName, and DOB fields. I need to retain one, say the first,
instance of the record but not delete all instances of similar records.

Thanks,
Phil....

 

[chriske911]

phil.... expressed precisely :

I have a table that has many similar but not duplicate records. What is the
same are the LastName, FirstName, and DOB fields. Other field values in these
records are different. How can I delete similar records based on repeating
LastaName, FirstName, and DOB fields. I need to retain one, say the first,
instance of the record but not delete all instances of similar records.

Thanks,
Phil....

create a query just holding those duplicate fields
add a calculated field which counts the number of same records and
delete all records holding a counter > 1

grtz

 

[Larry Linson]

create a query just holding those duplicate
fields add a calculated field which counts
the number of same records and delete all
records holding a counter > 1

I read the original post as saying one copy of the duplicates should be
retained and this doesn't seem to accomplish that.

Probably better to create a Totals Query, specifying GroupBy the Fields
being tested for duplicates, and using the (First) or other selection to
pick the other Fields. Then, when it is working the way desired, change it
to either a MakeTable or an Append Query.

Larry Linson
Microsoft Access MVP