Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    22

    Unanswered: Combining two tables with respect to individual data cells

    Howdy folks!

    Have a bit of a problem...developed a database for a client, works great.
    At some point they decided to back up all of their data, and unfortunately they were simultaneously using the original database as well as their backup for a few months.

    I need a way to merge the duplicate's master data table with the current database's. It is 255 columns by about 850 rows/records. The primary key for each record is a unique ID that is not generated by my database.

    I've tried using Access' append function, but it throws out duplicate primary key errors for all records. It seems the append function will only append completely new records, which is useless - data is constantly being added to each record, so this function is not sophisticated enough apparently.

    The next thing I tried was a union query. Unfortunately, the ~3000 character SQL statement automatically generated by Access has incorrect syntax. I tried to google the error that it spat out, which seemed to be related to the field names containing spaces, but even after I removed spaces from every field name of both tables and tried it again I experienced the same error.

    The latest attempt was exporting both tables as excel spreadsheets to try and use excel macros for combining. I haven't had any luck on this end as of yet, plus with this method I would have the lovely task of trying to reimport the table with all 255 columns being correct data types, some of which are "memo" and contain paragraphs of text.


    So basically, very simply:

    -Have two tables, same columns, same data types
    -For any empty cell in the original table, if the duplicate's table has any data there for the corresponding record, copy it

    and yet this seemingly simple task is proving to be quite difficult.

    Any suggestions are GREATLY appreciated! Thanks!!

  2. #2
    Join Date
    Jun 2008
    Posts
    22
    Oh, I'm using Access 2007, but the database is compatibility mode so it will work for 2003 as well.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    hunterw, need a little more information.

    What I understand.

    2 tables with identical structure.
    1 column is a Unique Id while the other 254 rows are data.
    Empty cells need data (if available).

    What I don't know.

    Do both tables have the same unique identifier for the same row? For example:

    Unique Id value of 1 on both tables is the 'same' row. (this would be good)

    or

    Table1 Unique Id of 1 and Table2 Unique id of 2 are the 'same' row. (this would be bad unless you had some other column to 'link' the rows together).

    Do both tables have the same number or rows? Does Table1 have some rows not in Table2 and/or Table 2 has some rows not in Table1?

    If the row from Table1 and Table2 can be 'linked' is it just a matter of putting data in the row that is blank or can one row have some data (ex. 'ABC 123') while the other row has different data (ex. CBA 321) (This would be in the bad category unless you know which row is the correct data).

    Assuming you have the same unique id in both tables and you just need to update the empty cells, something like this might work:
    Code:
    SELECT ID, MAX(COL1), MAX(COL2), etc.
    FROM(SELECT ID, COL1, COL2, etc.
         FROM TABLE1
         UNION ALL
         SELECT ID, COL1, COL2, etc.
         FROM TABLE2
        ) AS BOTHTAB
    GROUP BY ID
    I think this will Combine identical ID value while keeping the MAX of the value in one of the 2 columns. (if one is blank, the data will be the MAX. If both are Identical, 1 will be MAX but it doesn't matter which one. If the data is different this won't work).

    In addition if there are any rows in Table1 and/or Table2 not in the other table, those rows will be kept.

  4. #4
    Join Date
    Jun 2008
    Posts
    22
    Quote Originally Posted by Stealth_DBA
    What I don't know.

    Do both tables have the same unique identifier for the same row? For example:

    Unique Id value of 1 on both tables is the 'same' row. (this would be good)

    or

    Table1 Unique Id of 1 and Table2 Unique id of 2 are the 'same' row. (this would be bad unless you had some other column to 'link' the rows together).

    Do both tables have the same number or rows? Does Table1 have some rows not in Table2 and/or Table 2 has some rows not in Table1?
    Currently, no, but there are so few non-duplicate records that I could easily do this by hand - sort both tables by primary key, then add any missing to each table, so I'll go ahead and do this.


    If the row from Table1 and Table2 can be 'linked' is it just a matter of putting data in the row that is blank or can one row have some data (ex. 'ABC 123') while the other row has different data (ex. CBA 321) (This would be in the bad category unless you know which row is the correct data).
    It is possible that fields may have non-matching data, but this will be so rare that I am willing to neglect it. How do I go about linking them? The only linked tables I know of are simply external files "linked" to be used in the database.


    Assuming you have the same unique id in both tables and you just need to update the empty cells, something like this might work:
    Code:
    SELECT ID, MAX(COL1), MAX(COL2), etc.
    FROM(SELECT ID, COL1, COL2, etc.
         FROM TABLE1
         UNION ALL
         SELECT ID, COL1, COL2, etc.
         FROM TABLE2
        ) AS BOTHTAB
    GROUP BY ID
    I think this will Combine identical ID value while keeping the MAX of the value in one of the 2 columns. (if one is blank, the data will be the MAX. If both are Identical, 1 will be MAX but it doesn't matter which one. If the data is different this won't work).

    In addition if there are any rows in Table1 and/or Table2 not in the other table, those rows will be kept.
    Awesome - I assume that I would input this as a query, but I am still worried that I will experience the same errors I had before when trying to do a union query: Access makes the SQL statement that's a few pages long but then throws a syntax error when I try and run it...
    If I am misunderstanding please let me know! It may be that the error was due to primary keys not matching up.

    Making the primary keys match up on both tables can only help, so I will start there.

    Thanks!

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Link may have been a poor choice of a word as it has other meanings in Access. What I meant is something to identify that row in Table1 and row in Table2 are actually the same row. This can (probably) be done if they have the same Unique ID. But if the Unique Id is different, what other column(s) could be used to programatically indicate they are the same row.

    I think your original problem was you did an Append query. This was adding rows to an existing table and the Unique Ids already existed.

    In the example I gave, the final Select (the one that is Grouped with MAX) will only have distinct Unique ids. I would run this as a Select first and check the output. Or you can run it as an append to another identical but empty table.

    By leaving the two original tables untouched, you can run other queries along the lines of:

    SELECT A.unique_id, a.col1, b.col1
    FROM TABLE1 A, TABLE2 B
    WHERE a.unique_id = b.unique_id
    AND a.col1 (what you need to type to indicate not blank)
    AND b.col1 (what you need to type to indicate not blank)
    AND a.col1 <> b.col1

    This would find the rows where both rows have data but are not the same.

    (This could be expanded to include all 254 columns or run 1 column at a time.)

Posting Permissions

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