Merge duplicate/similar records into 1 record in Access 2010

[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!

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

[ranman256]

I would:
add all of tbl1 to a new table
then join new tbl to the second table
and 1 field at a time, update new tbl.fld (from tbl2.fld) if new.field = null

[rpeare]

what is the primary key? what if a field has more than 1 different response, for instance this person in your exampl ehad 123 anywheres st. and 123 anywhere st., a slight misspelling but they would be treated as different values.

If you are looking at compressing your data you can create a table that has the exact same fields you have now and create an AGGREGATE query (look for the sigma button on your query design toolbar that looks like a capital E)

in your TOTAL row, instead of having GROUP BY for all your fields (except your primary key field) put in either MIN or MAX you will get a single value for all your non primary key fields.

Once you've worked out if this is giving you what you want you can turn it into an APPEND query and attach it to your summarized table.

[andigirlsc]

What would the VBA code look like for this? Or, is this a query condition in design mode?

[andigirlsc]

I would:
add all of tbl1 to a new table
then join new tbl to the second table
and 1 field at a time, update new tbl.fld (from tbl2.fld) if new.field = null

What would the code look like for this? Is this a VBA statement I would need to write or a condition in a query?

[rpeare]

No vba involved really, just a SELECT aggregate query, once you verify results you can turn it into an APPEND query to a new table.

[andigirlsc]

what is the primary key? what if a field has more than 1 different response, for instance this person in your exampl ehad 123 anywheres st. and 123 anywhere st., a slight misspelling but they would be treated as different values.

If you are looking at compressing your data you can create a table that has the exact same fields you have now and create an AGGREGATE query (look for the sigma button on your query design toolbar that looks like a capital E)

in your TOTAL row, instead of having GROUP BY for all your fields (except your primary key field) put in either MIN or MAX you will get a single value for all your non primary key fields.

Once you've worked out if this is giving you what you want you can turn it into an APPEND query and attach it to your summarized table.

The primary key is the SSN (Social Security Number). I'm not sure what to do if some fields have slightly different values, hence the post for help. Thank you for bringing that to my attention, however. I will try this out and let you know if it works for me. Thank you!

[andigirlsc]

what is the primary key? what if a field has more than 1 different response, for instance this person in your exampl ehad 123 anywheres st. and 123 anywhere st., a slight misspelling but they would be treated as different values.

If you are looking at compressing your data you can create a table that has the exact same fields you have now and create an AGGREGATE query (look for the sigma button on your query design toolbar that looks like a capital E)

in your TOTAL row, instead of having GROUP BY for all your fields (except your primary key field) put in either MIN or MAX you will get a single value for all your non primary key fields.

Once you've worked out if this is giving you what you want you can turn it into an APPEND query and attach it to your summarized table.

This completely fixed my problem! Thank you so much! I searched for days and couldn't find an answer. This is it!