Delete Query

  • Thread starter Thread starter Lamar
  • Start date Start date
L

Lamar

I have a table that has duplicate records. I use a find
duplicate query to find the duplicate records. I
use "Customer ID" field to find the duplicate records.

I need to use a query to delete the duplicate records. I
need to delete the duplicates but keep one record with the
Customer ID.

I need to use a query or visual basic for everything I
do. I can not manually go into the table.

Is there a way to do this? Thanks for any help.
 
Hi Lamar,

Are these records exact duplicates? If so, I don't know that you could use
a delete query because all records would satisfy the same conditions and a
delete query that would delete the duplicate Company ID would also delete the
original.

If there are some fields that could differentiate the records (such as a
date the record was added, or updated), you could probably use a subquery to
rank them and delete all but the first rank for each ID.

If the duplicate records are exact duplicates, or if you don't care which of
the records is saved for a given Customer ID as long as one is, I think
possibly the easiest way to delete duplicates is to make a copy of the table
(structure only) and then set the Company ID as the Primary Key. Then run an
append query to append all records from the original table to the copy. The
primary key will automatically reject the duplicates.

Hopefully that will help. Post back with more info on how you want to
determine which record to keep if you would like more help.

-Ted Allen
 
Back
Top