how to delete similar records in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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....
 
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.
Click to expand...
Thanks,
Phil....
Click to expand...

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
 
chriske911 said:
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
Click to expand...

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
 
Back
Top