Access: Remove similar records with one duplicate field 1

[Hunguyen]

Hi,

I have one table called districts. The table contains 150K+ records. The first field (Field1) contains duplicate data, however their records are still unique. Can anyone help with a script to remove the duplicate field data and associated record and keep one instance of the duplicate data?

Ex.

FIELD1 FIELD2 FIELD3....etc there are 20 fields
V0N2J5 Mary Smith Wendy
V0N2J5 Mary Walsh Paula
V0N2J5 Mary Smith Jill
V0N3P9 John Doe Bill
V2C4N6 John Doe etc....

In the above example, I would like to retain records# 1, 4, and 5. For my purposes, where there is a duplicate in FIELD1, I would like the related records removed.

Thank you to anyone who can help me with this

 

[dhookom]

Why would you keep #1 and not #2 or #3? Can't say "because its the first" since there is no first or second or even last without specifying a sort order in a query.

Does your table contain a primary key? Could the value of this field be used to determine which record to keep?

Duane
Hook'D on Access
MS Access MVP

 

[HarleyQuinn]

Is there any particular criteria you'd apply to choose the record you want to keep or just keep one record randomly per Field1 value?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Hunguyen]

I would like to keep all the fields.
I have used Access to auto-generate a PK, but it will be deleted once duplicates (Field1) are removed.
It does not matter what duplicate value in Field1 that is retained; I just want to retain one of the duplicate values (Field1) and their associated record.

 

[Dontremb]

Hunguyen,

Are you saying that you want to change it from this:

FIELD1 FIELD2 FIELD3....etc there are 20 fields
V0N2J5 Mary Smith Wendy
V0N2J5 Mary Walsh Paula
V0N2J5 Mary Smith Jill
V0N3P9 John Doe Bill
V2C4N6 John Doe etc....

To this:

FIELD1 FIELD2 FIELD3....etc there are 20 fields
V0N2J5 Mary Smith Wendy Walsh Paula Jill
V0N3P9 John Doe Bill
V2C4N6 John Doe etc....

?

Are you trying to consolidate all your field ones, and move all the other fields that were associated with Field1 to the consolidated row?

 

[Hunguyen]

Hi Dontremb,

Yes, I do want to consolidate Field1 and keep all the fields associated with the particular row. All other duplicate Field1's and their associated records can be deleted.

Below is the result that I'm looking for:

FIELD1 FIELD2 FIELD3....etc there are 20 fields
V0N2J5 Mary Smith Wendy
V0N3P9 John Doe Bill
V2C4N6 John Doe etc....

 

[Dontremb]

Hunguyen,

Ah, well... That sounds easy enough... Err, probably not for ME, but at least I think we're all clear on what you're trying to do.

I think it would take a lot of code, basically, you're going to have to compare EVERY ID with your field1 to see if any of them are the same. THEN, you're going to have to compare all FIELD2-20 in row 1 with FIELD2-20 in row 2, see which ones are different, add the different ones to row 1, then delete row 2.

If you didn't have 150k records, I'd say just spend a day or two and do it by hand. But, it could be easier than I think, I just don't know the code.

 

[PHV]

I have used Access to auto-generate a PK
what is the name of this PK field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hunguyen]

Hi PHV,

The PK field is simply entitled, ID.

 

[PHV]

SQL code:
DELETE * FROM districts
WHERE ID Not In (SELECT Min(ID) FROM districts GROUP BY FIELD1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hunguyen]

Hi PHV,

I'm currently running the script that you had provided and it's about 1/3 completed after 1.5hrs. Is this typical for a dataset of 170K records?

 

[PHV]

I guess the delete query would run faster if FIELD1 was indexed.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hunguyen]

Thank you PHV,

The query worked great. I did have to run it overnight even after indexing FIELD1, but it did finally do what I wanted it to. Thanks again for your speedy reply!

-H