finding duplicates and deleting via many methods (1 Viewer)

[neuroman9999]

It's been a long time folks, since I've done this, but is this really a simple manipulation of what the Query wizard's ""find dups"" feature returns? see attached file. I've got 1500 recs. thanks.

 

[Pat Hartman]

As long as it doesn't matter which "dup" you save OR you can sort them so that the "dup" you want is first, then:
1. Turn off Name-auto-corrupt if you have left it on. If you don't, you'll be sorry. You can turn it back on when we're done if you insist. But I recommend turning it off and leaving it off unless you actually want it to change stuff for you. Then you can turn it on to help with renaming and turn it off again. This an extremely dangerous "feature" if you don't understand how it actually works.
2. Create a copy of the permanent table.
3. Define the PrimaryKey as an autonumber and create a unique index (multi-field if necessary) that will prevent future duplicates.
4. Run an append query to append the data to this table. Only the first of each "dup" set will be appended. The unique index will prevent others from being added.

Delete the original table and rename the dup back to the original name.

 

[neuroman9999]

I'm not following #4, Pat. can you explain that? sorry about that.

 

[Pat Hartman]

Once you define the table so that it has proper indexes to prevent duplicates, #4 is how you get the data from the old, incorrectly defined table that contains duplicates to the new, empty, properly defined table. Because of the proper indexing, only unique records will be copied. At the end of the query, you'll get an error message telling you how many records were discarded and why. Because you are using an append query, the original autonumber primary keys will be retained if you include the PK. This makes dealing with child tables easier. I'm guessing you don't have any child tables but if you do, that's going to be your next question - how to I connect the orphaned child records from the discarded "dups" to the single kept record?

If you want to actually examine the discarded records, you can use a left join between the old table and the new table that returns only rows with null in the new table's PK. Use the wizard. This is a typical unmatched query.