Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Location
    Columbia, SC
    Posts
    5

    Smile Unanswered: Merge duplicate/similar records into 1 record in Access 2010

    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 Im doing now) I do not have a solution.

    Thanks!

    Code:
    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
    Code:
    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I have researched this question numerous times and have not found an answer specific to my needs
    almost certainly becuase you have a poorly implemented table design and what you are trying to do is not a good call

    Yet, the primary key is the same for all duplicate records
    the primary key CANNOT be be the same for duplicated rows.... A primary Key uniquely identifies a single row. thats precisely why you have this current problem, because either you haven't specified a PRIMARY key or you have used an autogenerated ID. looking at your data the candidate key for this table would be SSN assuming that you always have the SSN available when creating a row in this table. if that fails then you may have to resort to a SURROGATE PRIMARY KEY such as an autogenerated key. however in that case you should still set a unique index on SSN but allow NULL values

    you cant use a combination of first & lastname as its very probable you can have more than one person with the same first and last names. all the other columns are transient data and therefore not suitable for a PK. indeed even if you thought that first & lastname was a suitable PK then that also fails if the person changes there name through marriage / deed poll / whatever your local jurisdiction allows

    if you want to have duplicated data and cause ongoing serious operational problems through data redundancy, duplication, uncertainly about which is the the 'right' row then by all means kludge your way round the problem

    that kludge could be running a series of update statements that set a value providing, however its such a 'orrible kludge Im not going to attempt to suggest the SQL to do so. youd need to only update the column if you had matching data and the target column was NULL

    fix your data, get rid of the problem through proper table design not applying a sticking plaster of a festering sore
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Location
    Columbia, SC
    Posts
    5

    Cool Resolved: Merge duplicate/similar records into 1 record in Access 2010

    I did not design the original tables. This data was taken from numerous spreadsheets by non-database users who used Excel. They in turned asked me to create a database. So, I imported numerous spreadsheets with redundant data into an Access database and am now working with the data to make it make sense. I understand table design, but this is what they gave me to work with. Table A has 500 records with SSNs as the primary key. Table B has 700 records (some that are already in Table A) and SSNs are the also the primary key here as well. Different people created these spreadsheets, so they didn't understand what kind of problems I would eventually run into. Thank you for your rebuke, but this just fell into my hands.

    Someone else posted a helpful response on another forum. Here is the link: Merge duplicate/similar records into 1 record in Access 2010

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Select t1.firstname, t1.lastname, t1.ssn, iif(isnull(t1.address),t2.address, t1.address) as addr......
    from mytable as t1
    inner join mytable as t2 on t1.ssn = t2.ssn
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •