delete records help

[Bob]

I have a table with a field "product_id". The table has a lot of records
with the same "product_id", I would like to write a query that will delete
all the records with the same "product_id" except one. I would like to
have just one record per "Product_id"

thank you for any help

 

[John Vinson]

I have a table with a field "product_id". The table has a lot of records
with the same "product_id", I would like to write a query that will delete
all the records with the same "product_id" except one. I would like to
have just one record per "Product_id"

thank you for any help

By far the simplest way to do this is to:

- rename your table (say to Products_Old)
- Copy and paste it to the old table name, choosing the option to copy
the design mode only
- Open the table in design view and make Product_ID the Primary Key by
clicking the key icon
- Run an Append query to append the data in Products_Old into the new
empty table. You'll get a warning message "xyz records could not be
appended due to key violations" - that's your dups.

Note that if you have six different records with the same ID but
*DIFFERENT* values in the other field, five of those records will be
discarded. You can sort the Append query to choose which record to
save (the first in the sort order) but you *will* lose data.

John W. Vinson[MVP]