Merge duplicate/similar records into 1 record in Access 2010 (1 Viewer)

[andigirlsc]

Is there a way to merge duplicate/similar Access 2010 records into one record? I have researched this question numerous times and have not found an answer specific to my needs.

I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand, (which is what I’m doing now) I do not have a solution.

Thanks!

Example

LastName  FirstName  SSN           Address          Phone         Email
Doe       John       123-45-7891   123 Anywhere St. NULL          john(at)gmail.com
Doe       John       123-45-7891   NULL            (123)456-7890  NULL

Desired Result

LastName  FirstName  SSN           Address          Phone         Email
Doe       John       123-45-7891   123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe       John       123-45-7891   123 Anywhere St. (123)456-7890 john(at)gmail.com

 

[plog]

You can do this in an aggregate query (using sigma/summation symbol). What you would do is create a query based on your table, bring down the primary key field, and then all other fields you want in the result, click the sigma/summation symbol. That will put 'Group By' under all the fields, change that to 'Max' for all but the primary key. Run that and it should do what you want.

If any of the 'duplicates' have different data for a field that is non-null it would return the value that is alphabetically last.

 

[Dlovan-IT]

you can use the sql , but one thing you needed , one column must be unique and have in the twice , lets say in there the last name field have the same name and not change you can doing it by this way

select LastName.Table1,FirstName.Table1,SSN.Table1,Address.Table1,Phone.Table1,Email.Table1,LastName.Table2,FirstName.Table2,SSN.Table2,Address.Table2,Phone.Table2,Email.Table2,
from Table1 , Table2 where LastName.Table1 = LastName.Table2