Delete duplicates, retain unique records

[nad341]

Hi,
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct... is not useful. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.

Please let me know the best way to do this.

Thanks
NAD

 

[CJ_London]

There are a number of ways to resolve this - the simplest is to use the following to create a new table with all unique records - not this assumes that you do not have an autonumber field in your existing data.

SELECT DISTINCT OldTable.* INTO NewTable FROM OldTable

If you have an autonumber field then use this

SELECT DISTINCT fld1, fld2,fld3... INTO NewTable FROM OldTable

excluding the autonumber field

Once done, you can rename NewTable to OldTable

 

[Cronk]

CJ

I agree with you that creating a new table is a quick solution, but you are aware of the problem that might exist if the Autonumber PK is used as a FK in some other table.

It then becomes a non trivial solution that I have had to implement a number of times.

I've used VBA to loop through the instances of the multiple occurrences, selecting one to retain, then changing all the foreign keys in the other table for the other occurrences to the PK to be retained and then deleting the duplicate.

 

[CJ_London]

Cronk - I am aware and quite agree but the OP has not provided sufficient information to determine if this is the case. The only reason I mentioned the auto number field is it is standard practice to include one or an equivalent in table design - it doesn't mean it is being used as a FK in related records.

 

[ButtonMoon]

Nobody has yet mentioned what might be the most important part of the solution: add a uniqueness constraint to prevent any more duplicate data in future. In other words don't just mop the floor; fix the leaking pipe as well!

 

[nad341]

I have 1-2 fields in the duplicate records that do not match. So, using distinct would not be helpful. The actual table from where these records need to get deleted has additional fields that match.

I tried copying the structure, defining primary key and appending the records from the current table into the table with Primar keys, but that won't work since I am going to have multiple EIDs with different Filing_Dates.

Can the use if First function work in this case?

 

[Pat Hartman]

Well those are the two solutions you can implement with queries. The non-important data gets discarded arbitrarily.

If you need to retain the multiple filing dates, you'll need to create a second table to hold them. You first need to use either technique to get to a table of unique "key" values. Then you would join the new table back to the original and append the dates into the child table. You would pick up the PK of the new table as the FK for the child table.