Eliminate duplicate entries

[fboehlandt]

Hi everyone

I am looking for a way to merge duplicate rows of a table. I suspect that the query wizard might be able to help. I include some sample data below:

[Customer ID] [Customer_Name] [Address] [Phone]
[1001] [Bob Smith] [Main Rd 1, NY] [555-1234]
[1002] [John Miller] [Oak Ln 2, NY] []
[1002] [John Miller] [] [555-9876]

The resulting table should be

[Customer ID] [Customer_Name] [Address] [Phone]
[1001] [Bob Smith] [Main Rd 1, NY] [555-1234]
[1002] [John Miller] [Oak Ln 2, NY] [555-9876]

We can assume that Customer ID reliably identifies duplicate entries.

Any help is appreciated

Reagrds

Florian

 

[isladogs]

If CustomerID is the PK field you won't get duplicates in the first place.
In which case you can just use a standard update query

If it isn't the PK field, I would modify your table.

If not, use the wizard to check for duplicates
Then create an update query for duplicate records

The obvious issue you that you need to tell Access which record contains the data to use in the merged record. Easy if one record has an empty or null field as in your examples.
BUT what if both records contain data? Which record would you use?

 

[fboehlandt]

Hi there

thanks for your quick response. Just a few comments:

- Customer ID is not the PK
- The problems result from poor data entry that did not address the possibility of row duplication
- I agree with your assessment of non-NULL fields which will require to prioritize data from one row over another

I thought the following approach might work:

1. Create a table containing duplicates plus all attribute data
2. Create a second table from the original table without duplicates
3. Write a JOIN query including IIF functions to check for NULL values

This does not address the non-NULL issue but I might be okay with that. What are your thoughts?

 

[isladogs]

The main thing is to ensure no dupes occur in future so creating a new table is in my view s good approach. Populate the new table with non dupe data then delete the original table.

You'll need to check the data to decide whether non Null values are an issue for you

I'd also recommend you remove all spaces from field names