Results 1 to 6 of 6
  1. #1
    fzxxx is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2020
    Posts
    7

    Merge two tables

    Hi. I have a task that should be easy with an Access query.
    I have 2 table (imported from Excel), with a common field (MYCODE), while the other fields are different.
    I made a simple relationship between the two tables.

    Table1
    MYCODE field1 field2
    AAA a a
    BBB b b
    CCC c c

    Table2
    MYCODE field3 field4 field5
    BBB b b b
    DDD d d d
    EEE e e e

    I want merge data into NEW_TABLE
    Final table must have all rows, see below:

    MYCODE field1 field2 field3 field4 field5
    AAA a a - - -
    BBB b b b b b
    CCC c c - - -
    DDD - - d d d
    EEE - - e e e

    I am trying with UNION Query, such as

    SELECT MYCODE INTO NEW_TABLE
    FROM (SELECT MYCODE from Table1
    UNION
    SELECT MYCODE from Table2)


    This creates the 5 row (this is correct), but how to include all fields from the tables in the SQL?

    when I try it in design I see a JOIN is added automatically
    SELECT Table1.MYCODE, Table1.field1, Table1.field2, Table2.field3, Table2.field4, Table2.field5 INTO NEW_TABLE
    FROM Table1 inner JOIN Table2 ON Table1.MYCODE = Table2.MYCODE;
    with this JOIN not all the rows are inserted...



    Not sure hoe to go on...

    Thanks for your suggestions
    F

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Maybe try something like:

    SELECT MYCODE, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5 INTO NEW_TABLE
    FROM (SELECT MYCODE, FIELD1, FIELD2, "" AS FIELD3, "" AS FIELD4, "" AS FIELD5 from Table1
    UNION
    SELECT MYCODE, "" AS FIELD1, "" AS FIELD2, FIELD3, FIELD4, FIELD5 from Table2)

  3. #3
    fzxxx is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2020
    Posts
    7
    Quote Originally Posted by JoeM View Post
    Maybe try something like:

    SELECT MYCODE, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5 INTO NEW_TABLE
    FROM (SELECT MYCODE, FIELD1, FIELD2, "" AS FIELD3, "" AS FIELD4, "" AS FIELD5 from Table1
    UNION
    SELECT MYCODE, "" AS FIELD1, "" AS FIELD2, FIELD3, FIELD4, FIELD5 from Table2)
    ok, thanks, however it creates this table with 6 recs instead of 5

    MYCODE field1 field2 field3 field4 field5
    AAA a a - - -
    BBB - - b b b b
    BBB b b - - -

    CCC c c - - -
    DDD - - d d d
    EEE - - e e e

    with 2 BBB records

    anybody can give me hint on an easy way to compact the table? (and, by the way, is "compact" the right word? Googling this word gives me lot of incorrect results... )

    Thank you for all suggestions

    F

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, that is because a UNION doesn't have any JOINS/RELATIONSHIPS. It just "stacks" all the records together.
    You have a few options here. You could work with this result, and try to do an Aggregate Query on the results (Group By MyCode, and try different Aggregates on the fields, depending on their Data Types).

    Or, you could do the following:
    1. Create a Query between Table1 and Table2, joining on the MyCode field, and selecting the Join option where you keep all records from Table1, and just the matching records from Table2. Be sure to return all 5 fields from these tables.
    2. Create another Query, an Unmatched Query between Table2 and Table1, that returns all records from Table2 that do not have matches in Table1.
    3. Create a Union Query between the first two queries to combine all your records. Be sure to add the "space" holders for Fields 1, 2, and 3 when adding in the second query (much like I showed you in my previous post).

    That should give you what you want.

  5. #5
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    I would be tempted to do this in two steps.

    1. UNION query to get all the data in the correct columns and rows.

    2. APPEND query to add them to the new table.


    example.
    Code:
    SELECT mycode, field1, field2, "" AS field3, "" AS field4, "" AS field5
    FROM table1
    
    UNION
    
    SELECT mycode, "" AS field1, "" AS field2, field3, field4, field5
    FROM table2
    then,

    Code:
    INSERT INTO newtable (mycode, field1, field2, field3, field4, field5)
    SELECT mycode, field1, field2, field3, field4, field5
    FROM union_query
    WHERE (((union_query.mycode) Is Not Null));

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You may want to consider loading the two tables into Power Query and running a table merge. Look at this link. This works for whether it is an Excel or Access Table.

    https://trumpexcel.com/merge-tables/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Merge two tables
    By joethall in forum Access
    Replies: 8
    Last Post: 02-06-2019, 03:59 PM
  2. Merge Tables in Access Web App
    By dz227 in forum Macros
    Replies: 0
    Last Post: 01-17-2017, 12:57 PM
  3. Merge Tables
    By cfobare79 in forum Access
    Replies: 21
    Last Post: 05-02-2015, 03:53 PM
  4. Don't know if I should merge tables (please help)
    By bigdaddy757 in forum Database Design
    Replies: 2
    Last Post: 05-30-2013, 01:52 PM
  5. How do I Merge Four Tables Together
    By SteveI in forum Queries
    Replies: 1
    Last Post: 03-04-2010, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums