Deleting similar (not duplicate) records

[pmccue]

I need to remove repeating instances of records where the Name and DOB field values are the same. Other field values in these same records are different, so the records are not exact duplicates, but similar. I'd retain in the table the first instance of a similar record. Is there a script to do this? All attempts at this so far have failed. I'll need a script example as I don't have formal sql training. Or perhaps it can be done in Access directly. Thanks for your continued help on this matter.

PHil....

 

[rjoubert]

One way to do this in Access is to make a copy of the source table (just the structure, not the data). Then, edit the structure of the copy, and make the Name and DOB fields the key. After that, create an append query that appends the records from the source table into the copy of the table. You will get an error message saying it can't append all of the records...click Yes on that error. Your copy of the table will now unique records (by Name and DOB).

 

[pmccue]

rjoupert,

Thanks for your reply!
I copied Table1 structure only & saved as Table1_Copy...worked OK.
Cannot assign keys to multiple fields in Table1_Copy (Name and DOB) as Access only allows one key in a table. Any ideas here?

Thanks,
Phil....

 

[rjoubert]

In table design view, you can highlight multiple fields, then click on the key button in the toolbar. To highlight multiple fields, click and hold on the first one (in the gray area to the left of the field name) and drag your mouse down over the other(s).

 

[PHV]

Access only allows one key in a table
ONE key, MULTIPLE fields (aka composite key)
Select the 2 fields and then click the key icon.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pmccue]

TO rjoupert & PHV,

OK, I FINALLY got it to work!

Thanks,
Phil....

 

[PHV]

You issue was resolved here:
thread701-1260448 29 Jul 06 20:00