Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2011
    Posts
    8

    Unanswered: Can I use UNION with INSERT INTO in SQL View

    I am trying, without any luck, to pull the information from 1 field in 2 different tables, combine them into 1 longer field in another table.

    This Query will combine the PartNumber fields from both ProdPartNoSample and ProtoPartNoSample tables.
    SELECT ProdPartNoSample.[PartNumber]
    FROM ProdPartNoSample
    UNION SELECT ProtoPartNoSample.[PartNumber]
    FROM ProtoPartNoSample;

    This Query will take the PartNumber Field from ProtoPartNoSample and put that information into PullPartNo Table in PartNo Field.
    INSERT INTO PullPartNo ( PartNo )
    SELECT ProtoPartNoSample.PartNumber
    FROM ProtoPartNoSample;

    I cannot however combine these Query's without getting Syntax Errors.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not seem to exactly know what a UNION query does. A UNION query will return as many rows as there are in each table it references (i.e. if table A has 5 rows and Table B has 3 row, a UNION query merging both will yield 8 rows)

    To add fields from different tables into a single row you use a SELECT query with a JOIN linking several tables (e.g. SELECT Table1.Column1, Table2.Column2 FROM Table1 INNER JOIN Table2 ON Table1.Row_ID = Table2.Row_ID;)

    To concatenate the values form two or several fields into a single field you use a SELECT query - with or without JOIN - with a concatenation operator (e.g. SELECT Table1.Column1 & Table1.Column2 AS BigColumn FROM Table1;).

    See: Combine the results of several select queries by using a union query - Access - Office.com
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    8
    You're right I'm basically a novice trying to stumble through this.

    Looking at your examples I'm still unclear how to get my combined fields into a new table.

    I will do some more reading tomorrow.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need a column (a field) that can be used to create a relationship (a JOIN) between both tables. In my second example, this field is Row_ID and the relationship is defined as: Table1.Row_ID = Table2.Row_ID.

    This means that each row in Table1 has a Row_ID field, that each row in Table2 also has a Row_ID field and that there is a correspondence between row in both tables having the same Row_ID value. You need to determine such a relationship to solve your problem:

    Code:
    INSERT INTO PullPartNo ( PartNo )
    SELECT ProdPartNoSample.PartNumber & ProtoPartNoSample.PartNumber AS LongPartNo
        FROM ProdPartNoSample 
        INNER JOIN  ProtoPartNoSample
        ON ProdPartNoSample.XXX = ProtoPartNoSample.YYY
    Where XXX and YYY are the names of the fields that are related in both tables.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    8
    I tried your code based on the primary ID key.

    INSERT INTO PullPartNo ( PartNo )
    SELECT ProdPartNoSample.PartNumber & ProtoPartNoSample.PartNumber AS LongPartNo
    FROM ProdPartNoSample
    INNER JOIN ProtoPartNoSample
    ProdPartNoSample.ID = ProtoPartNoSample.ID


    This puts the information from ProdPartNoSample.PartNumber and ProtoPartNoSample.PartNumber together into the same field. Essentially adds the information together.

    What I am trying to do is create a field(column)with all the information from ProdPartNoSample.PartNumber followed in the next rows same field by the information in ProtoPartNoSample.PartNumber

    For Example:
    Table1 = ProdPartNoSample
    ID,PartNumber
    1,045T
    2,052T
    3,099T

    Table2 = ProtoPartNoSample
    ID,PartNumber
    1,039T
    2,022T
    3,016T

    New Table = PullPartNo
    ID,PartNo
    1.045T
    2,052T
    3,099T
    4,039T
    5,022T
    6,016T

    This Query gets the information into the format I want but I cannot figure out how to write this to the PullPartNo table.
    SELECT ProdPartNoSample.[PartNumber]
    FROM ProdPartNoSample
    UNION SELECT ProtoPartNoSample.[PartNumber]
    FROM ProtoPartNoSample;

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    What you are trying to do is against relational database normalisation.

    If you have a query that returns the data you want, you shouldn't put it into a table field, you should instead run the query whenever you require that information.

    Can you explain why you need to have these combined ID's in this new table?
    Looking for the perfect beer...

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is not what could be understood from what you wrote:
    Quote Originally Posted by Superchargingmach View Post
    I am trying, without any luck, to pull the information from 1 field in 2 different tables, combine them into 1 longer field in another table.
    If now you changed your mind and want to insert the rows of two tables into a third (this was the first case in my answer), you can indeed use a UNION QUERY but you must encapsulate it in another SELECT query (i.e. "hide" the UNION operation in a subquery if you want):
    Code:
    INSERT INTO PullPartNo ( PartNo )
    SELECT * FROM (
            SELECT ProdPartNoSample.PartNumber 
              FROM ProdPartNoSample
        UNION
            SELECT ProtoPartNoSample.PartNumber 
              FROM ProtoPartNoSample
    );
    This is equivalent to using two queries:
    Code:
    INSERT INTO PullPartNo ( PartNo )
    SELECT ProdPartNoSample.PartNumber 
      FROM ProdPartNoSample;
    Code:
    INSERT INTO PullPartNo ( PartNo )
    SELECT ProtoPartNoSample.PartNumber 
      FROM ProtoPartNoSample;
    Have a nice day!

  8. #8
    Join Date
    Sep 2011
    Posts
    8
    I apologize for the confusion. Combine was the wrong word to use.

    I agree these 2 pieces of code work:
    INSERT INTO PullPartNo ( PartNo )
    SELECT ProdPartNoSample.PartNumber
    FROM ProdPartNoSample;


    INSERT INTO PullPartNo ( PartNo )
    SELECT ProtoPartNoSample.PartNumber
    FROM ProtoPartNoSample;



    I get an error with this code. I simply cut and pasted your code.
    INSERT INTO PullPartNo ( PartNo )
    SELECT * FROM (
    SELECT ProdPartNoSample.PartNumber
    FROM ProdPartNoSample
    UNION
    SELECT ProtoPartNoSample.PartNumber
    FROM ProtoPartNoSample
    );


    Access accepts or will save this code but I get this error when I try and execute the query.
    The INSERT INTO statement contains the following unknown field name: 'PartNumber'. Make sure you have typed the name correctly, and try the operation again.


    When I open(edit) the Query it looks like this:
    INSERT INTO PullPartNo
    SELECT *
    FROM [SELECT ProdPartNoSample.[PartNumber]
    FROM ProdPartNoSample
    UNION SELECT ProtoPartNoSample.[PartNumber]
    FROM ProtoPartNoSample]. AS [%$##@_Alias];

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The transformation of the query is normal, at least with Access. However I don't understand why it does not run. Here's my version (the names are different in my test database):
    Code:
    INSERT INTO Table100 (Ref_ID)
    SELECT *
    FROM [SELECT Table102.Ref_ID
            FROM Table102
        UNION
            SELECT Table101.Ref_ID
            FROM Table101
    ]. AS [%$##@_Alias];
    And it works like a charm with Access 2003 (11.8321.8333) SP3 (I did not try with other versions of Access).

    First check if the names are correct according to the names of the columns in both tables. If it still does not work, try:
    Code:
    INSERT INTO PullPartNo ( PartNo )
    SELECT Part_No AS PartNo
    FROM [SELECT ProdPartNoSample.PartNumber AS Part_No 
            FROM ProdPartNoSample
        UNION
            SELECT ProtoPartNoSample.PartNumber  AS Part_No 
            FROM ProtoPartNoSample
    ]. AS [%$##@_Alias];
    Have a nice day!

  10. #10
    Join Date
    Sep 2011
    Posts
    8
    Thank you so much for your help.

    I'm guessing there must be some typographical error somewhere in the query. I will try this as soon as I can and report back.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Sep 2011
    Posts
    8
    Sinndho

    The second bit of code worked. I never did find a typo to explain why the first piece of code wouldn't work.

    Is Part_No an alias or temporary table?

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's an alias for both ProdPartNoSample.PartNumber and ProtoPartNoSample.PartNumber
    Have a nice day!

  14. #14
    Join Date
    Sep 2011
    Posts
    8
    Would it be possible to change this query so that it can be periodically run and append new records from ProdPartNoSample and ProtoPartNoSample and add the new records to PullPartNo?

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It depends on how you define "periodically" I guess.

    It can be run when the database is open, it can be run when a form or a report is open, it can be run in the Timer event handler of an open form, it can be run by a macro, called by a custom menu, etc.

    It can even be run by an external program, a VBScript file that can be called by the Scheduler of Windows, for instance.
    Have a nice day!

Posting Permissions

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