Another duplicate delete question...

[DAW]

Have searched the forum but cannot find a suitable answer...

I have duplicates in my table that I need to delete but leave one existing record, e.g.:

ID Part Family Colour Cost
1 123456 HHH M98765 12.99
2 123456 HHH M64975 12.99
3 123456 HHH E79461 12.99
4 123456 HHH G13467 12.99
5 123456 HHH M98765 12.99

In the above example i would want to delete records 2-5 (based on Part and Family, even though the colours are different) leaving one there. If I run the find duplicates and then change that to a delete query then it deletes them all.

This needs to be done via a query as it is done in automation, so manually copying the table structure and applying primary keys to the fileds etc. is not an option.

Any bright ideas?

 

[neileg]

Not elegant but it should work. Create a new table, copy the data into it, delete the data in the original table. Create a query based on the temporary table, concatenate Part and Family in a calculated field. Make this a totals query and use First on the concatenated field. This will select only one record. Then append this data back to the original table. Delete the data in the temporary table ready for the next time.