Removing doubles from a table 1

[VBAguy22]

Hello all,
I have a table with some info. So happened, that the person who made that table mistakenly added doubles to it. So that the EXACTLY same record appears twice or three times in the table. How do I get rid of the extra copies of the same record, and leave only one of them?

The table is already generated and can not be re-generated.

Thanks
Alex

 

[JonFer]

First add an AutoNumber field to the table.

Write a group by query that groups the fields in the table that indicate a duplicate. Add a field called:

DeleteNum: Max(AutoNumber)

Add another field

Dupes: Count(*)

And set it's criteria to >1.

This query will show you the highest AutoNumber value for duplicate rows. Now run a delete query like this:

Delete * from yourtable
Where AutoNumber In (Select DeleteNum from yourquery

Each time you run this query it will delete the highest duplicate number until all duplicates are gone.

 

[alr0]

Another easy way is to take the fields that make a record unique and concatenate them into a new temporary field.

NewField=[field1]&[field2]&[field3]

Then make a copy of the table structure only and make the new field a primary key.

Now when you import the data into the new table structure, each record will only be imported once. The duplicates will be key violations. You can delete the extra field after the import.

Pretty simple if this fits the data.

Hope this helps,

alr

 

[JonFer]

alr0 - You have to be careful concatenating fields like that to produce a unique key. Consider these records:

field1 field2 field3
000 100 200
00 0100 200

These produce the same concatenated "key" but are really different records. To be safe, add a delimiter between the fields that should not be present in any of the fields (e.g. ~~ or ^^)

 

[dante1963]

Another way, if the records are entirely identical, is to use a simple SQL command:

select distinct *
into (new table name)
from (old table name);

Then, simply delete the old table and rename the new one.

 

[alr0]

Hi Jonfer,

That is an interesting consideration.

If the new field is a text field everything should be ok. In fact the data in your example would have to be non-numeric to even exist with leading 0’s.

However I stand corrected. If you make a numeric field to concatenate text fields with numbers you could have the problem described. I suppose using a separator is advisable since it would not hurt in most cases.

I normally do not do this because I need to find duplicates where 7 fields are concatenated and only 4 have data but it could be a different 4 fields. In this case using a separator would prevent finding some dupes.

Thanks,

alr

 

[JonFer]

alr - You could still have problems with numbers concatenated to a text field such as this situation:

1111 111 221
111 1111 221

I had to do something like this a while back and ran into the problem. Concatenating without a separator is only really safe if each field always has the same number of characters/digits.

John

 

[alr0]

Hi John,

I see what you mean. I am working with 7 or 8 name and address fields so it is not an issue here but it sure could be with different data.

alr