Remove Duplicate Records

[NSearch]

Hi All,

Again, thank you in advance for help with this.

I am trying to find the fastest way to delete duplicate records from a very large table (rows, not columns).

Table Sample Attached: table_sample.zip

I would like to keep 1 record, but delete all records that have duplicates (for every field).

Thanks!

[June7]

Build an aggregate query and save to a new table.

SELECT DISTINCT Sheet1.* INTO test FROM Sheet1;

[NSearch]

Hi June,

Thanks for the message, but I am actually looking to remove the duplicate lines, not create a new table.

If all fields of a record match all fields from another record, I would like to delete 1 instance of the record.

If all fields of a records match all fields from 10 other records, I would like to delete 9 instances of the record.

I hope that makes sense.

I added an AutoNumber (ID). Please see attached: table_sample_v2.zip

Thanks for the help!

[isladogs]

I haven't looked at your example

The fastest way is:
1. Rename your table e.g. with OLD at the end of the name
2. Copy the table design & save with the original table name
3. Add a unique composite index to the fields which are getting duplicated.
4. Export your data to the new table. Due to the indexes, it will only allow one of each duplicate record
5. Check your new table & if all is OK delete the OLD table

If you want more control over which duplicate record is retained, there is another way which I prefer ...but it involves more work

[NSearch]

Thanks isladogs for the reply.

This isn't going to be a one time occurrence -- it will be a query used in table maintenance, possibly run up to 10+ times a day.

[JoeM]

I would do this in the following manner:
1. Make sure that your table has a Unique Autonumber field in it
2. Create an Aggregate Query, that groups on ALL fields except for the Autonumber field (and have it return the MIN value of the Autonumber field)
3. Create a Delete Query that deletes all records from your table where the Autonumber field is not found in the Aggregate Query you created in step 2

Once you have set this up, you only need to run the Delete Query each time (one step).

[isladogs]

Unless I'm misunderstanding by not reading this properly, once you've setup composite indexes in your table and removed existing duplicates as described, it will be impossible for new duplicates to occur in the future.

[NSearch]

This is what I came up with, but it takes a very long time to run the query. Does anyone have any suggestions for a faster way to accomplish this.

DELETE *
FROM tracking_number_history
WHERE id NOT IN (SELECT min_id 
                  FROM   (SELECT Min(id) AS min_id, 
                                 client_code, 
                                 carrier_code, 
                                 tracking_number, 
                                 invoice_number, 
                                 account_number, 
                                 freight_charges, 
                                 invoice_date, 
                                 transaction_date, 
                                 pickup_record_number, 
                                 receiver_name, 
                                 receiver_company_name, 
                                 receiver_address_line_1, 
                                 receiver_address_line_2, 
                                 receiver_city, 
                                 receiver_state, 
                                 receiver_postal, 
                                 receiver_country_territory 
                          FROM   tracking_number_history 
                          GROUP  BY client_code, 
                                    carrier_code, 
                                    tracking_number, 
                                    invoice_number, 
                                    account_number, 
                                    freight_charges, 
                                    invoice_date, 
                                    transaction_date, 
                                    pickup_record_number, 
                                    receiver_name, 
                                    receiver_company_name, 
                                    receiver_address_line_1, 
                                    receiver_address_line_2, 
                                    receiver_city, 
                                    receiver_state, 
                                    receiver_postal, 
                                    receiver_country_territory));

[JoeM]

If you go with Colin's approach, one you have gone through those initial steps, it will not allow you to add duplicate records going forward (those will be rejected).
So there wouldn't be any need to run any code or processes at all.

[NSearch]

Unless I'm misunderstanding by not reading this properly, once you've setup composite indexes in your table and removed existing duplicates as described, it will be impossible for new duplicates to occur in the future.

isladogs,

Once I have the composite index setup for the table and I use an insert statement from VBA (excel), will the duplicate key be ignored, while inserting all other non-duplicate rows OR will the query fail because a key violation occurred?

What I would like is for it to ignore the duplicate records and proceed with inserting all non-duplicated records.

Thanks!

[JoeM]

Once I have the composite index setup for the table and I use an insert statement from VBA (excel), will the duplicate key be ignored, while inserting all other non-duplicate rows OR will the query fail because a key violation occurred?

What I would like is for it to ignore the duplicate records and proceed with inserting all non-duplicated records.

When you import the Excel file manually, it gives you a warning that some records will fail because of a key violation, and it asks you if you want to proceed anyway. If you click "Yes", it imports the new unique records and drops the duplicates. Depending on how you are writing your VBA, it should behave in the same manner. There is an easy way to find out - test it and see it works!

[isladogs]

I think Joe has already answered your question about future imports.
If there are duplicates in the import, the indexes will only allow the first record to be imported.
The important thing is to design the composite index correctly to ensure this works properly.

Once you know that is true by testing it, you can change from running an append query to executing an equivalent sql statement.
That means you will no longer see the messages about 'X records were not imported. Do you want to continue?'

[NSearch]

I setup a composite index on the following 10 (max allowed) fields:



..and duplicates are still being added to the table.

Any ideas?

Thanks

[isladogs]

Which fields now contain duplicates?,

[NSearch]

Which fields now contain duplicates?,

client_code,
carrier_code,
tracking_number,
invoice_number,
account_number,
freight_charges,
invoice_date,
transaction_date,
pickup_record_number,
receiver_name,
receiver_company_name,
receiver_address_line_1,
receiver_address_line_2,
receiver_city,
receiver_state,
receiver_postal,
receiver_country_territory