delete similar records in Access

[Guest]

I have an Access table with some similar records where Name and DOB field
values are identical while other field values are different. How can I create
a table where each Name & DOB is unique? Would probably take first instance
of similar records in the resultant table. If sql script is required I'd need
a sample as I don't have formal training in this.

Thanks,
Phil....

 

[Graham R Seach]

Phil,

The easiest way is to (a) create a temp table into which you store only one
of each duplicated rows (probably using SELECT...INTO), (b) delete all the
duplicate rows from the original table, (c) copy the rows created in the
first step back into the original table, then (d) delete the temp table.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Graham,

Thanks for the info but the records are not duplicates, but similar (Name
and DOB fiels are repeated) but other fields differ. Any ideas?

 

[Guest]

I got the following instructions from this site:
Do you want to permanently delete all but one of each duplicate? If so,
here is an outline of one method to delete all but one of the duplicates.
On a copy of the database
--copy the table structure only (copy and paste)
--Set an index (no duplicates) on the field that you used to determine
duplicates. If you used multiple fields you will need to set a multi-field
index
--Create an append query that will append all the records from the original
into the copy. You will get an error saying xx records can't be appended
due to...The query will append one of the duplicate records and drop the
others.
--After the append runs, open up the new table and see if all the records
you want are there. If so, rename the original (just in case) and then name
the copy with the original name.

 

[Guest]

Lloraine,

Thanks for the input. Does this method apply if the records in question are
not duplicates, but similar records (2 field values, Name and DOB are
repeated but other fields in these same records contain different values)?

 

[Guest]

I would think so. The part that says, "Set an index (no duplicates) on the
field that you used to determine duplicates. If you used multiple fields you
will need to set a multi-field index" would determine what records to delete
no matter if some of the other fields are not duplicates. By making an index
(no duplicates) on the field(s), you are only going to get one record.

 

[Guest]

Well I tried your suggestion and all went well, except when I assigned a
multi-index (to Name and DOB fields) it wouldn't allow Index without
duplicates, stating that duplicates aleady existed. Is this correct? I left
Duplicates OK in theProperties and ran the query but duplicate Name and DOB
fields showed up in the new table.

 

[Pieter Wijnen]

you need to make an empty copy of the table & then make the unique key
before inserting the data from the old table
(the point of the exercise)

Pieter