Getting a Syntax error in FROM clause with UNION ALL query

Question

I have a Union All query that I currently run a make table query from. I am trying to consolidate them into one thing and have the union all query also just make the table. I am learning on the fly here any have been trying the INTO method which I can get to work as just a make table but as soon as I add the UNION ALL I get the syntax error.

Any help on what I am doing wrong would be greatly appreciated.

SELECT *
INTO tbl_Test
FROM
    (SELECT 
         qry_BomLevel1_PartNo.Type, qry_BomLevel1_PartNo.PartNo, 
         qry_BomLevel1_Desc.Description, qry_BomLevel1_Rev.Revision, 
         qry_BomLevel1_PartNo.Qty, qry_BomLevel1_Comment.Comment
     FROM 
         qry_BomLevel1_PartNo 
     LEFT JOIN 
         ((qry_BomLevel1_Desc 
     LEFT JOIN 
         qry_BomLevel1_Rev ON (qry_BomLevel1_Desc.XRefConfigurationID = qry_BomLevel1_Rev.XRefConfigurationID) 
                           AND (qry_BomLevel1_Desc.ChildRevNr = qry_BomLevel1_Rev.ChildRevNr) 
                           AND (qry_BomLevel1_Desc.ChildID = qry_BomLevel1_Rev.ChildID)) 
     LEFT JOIN 
         qry_BomLevel1_Comment ON (qry_BomLevel1_Rev.XRefConfigurationID = qry_BomLevel1_Comment.XRefConfigurationID) 
                               AND (qry_BomLevel1_Rev.ChildRevNr = qry_BomLevel1_Comment.ChildRevNr) 
                               AND (qry_BomLevel1_Rev.ChildID = qry_BomLevel1_Comment.ChildID)) 
          ON (qry_BomLevel1_PartNo.XRefConfigurationID = qry_BomLevel1_Desc.XRefConfigurationID) 
          AND (qry_BomLevel1_PartNo.ChildRevNr = qry_BomLevel1_Desc.ChildRevNr) 
          AND (qry_BomLevel1_PartNo.ChildID = qry_BomLevel1_Desc.ChildID)

    UNION ALL

    SELECT 
        qry_BomLevel2_PartNo.Type, qry_BomLevel2_PartNo.PartNo, 
        qry_BomLevel2_Desc.Description, qry_BomLevel2_Rev.Revision, 
        qry_BomLevel2_PartNo.Qty, qry_BomLevel2_Comment.Comment
   FROM 
        qry_BomLevel2_PartNo 
   LEFT JOIN 
       ((qry_BomLevel2_Desc 
   LEFT JOIN 
       qry_BomLevel2_Rev ON (qry_BomLevel2_Desc.ChildID = qry_BomLevel2_Rev.ChildID) 
                         AND (qry_BomLevel2_Desc.ChildRevNr = qry_BomLevel2_Rev.ChildRevNr) 
                         AND (qry_BomLevel2_Desc.XRefConfigurationID = qry_BomLevel2_Rev.XRefConfigurationID)) 
   LEFT JOIN 
       qry_BomLevel2_Comment ON (qry_BomLevel2_Rev.ChildID = qry_BomLevel2_Comment.ChildID) 
                             AND (qry_BomLevel2_Rev.ChildRevNr = qry_BomLevel2_Comment.ChildRevNr)
                             AND (qry_BomLevel2_Rev.XRefConfigurationID = qry_BomLevel2_Comment.XRefConfigurationID)) 
         ON (qry_BomLevel2_PartNo.XRefConfigurationID = qry_BomLevel2_Desc.XRefConfigurationID) 
         AND (qry_BomLevel2_PartNo.ChildRevNr = qry_BomLevel2_Desc.ChildRevNr)  
         AND (qry_BomLevel2_PartNo.ChildID = qry_BomLevel2_Desc.ChildID))

** I edited the code in here cause when I tried to add it as an answer it said it was to long. Ill get better at this. Thank you for the help as this got me up and working.

score 0 · Answer 1 · answered Jul 15 '19 at 17:06

MS Access creates nightmares with all it's ridiculous, useless parenthesis. If you copy your query out into a text editor, like Notepad++ which is free, that highlights opening & closing delimiters it will make jobs like this much easier.

I have no idea if this will do what you want it to do, but it should clear the syntax error you're currently getting.

SELECT *
INTO tbl_Test
FROM
    (SELECT 
         qry_BomLevel1_PartNo.Type, qry_BomLevel1_PartNo.PartNo, 
         qry_BomLevel1_Desc.Description, qry_BomLevel1_Rev.Revision, 
         qry_BomLevel1_PartNo.Qty, qry_BomLevel1_Comment.Comment
     FROM 
         qry_BomLevel1_PartNo 
     LEFT JOIN 
         ((qry_BomLevel1_Desc 
     LEFT JOIN 
         qry_BomLevel1_Rev ON (qry_BomLevel1_Desc.XRefConfigurationID = qry_BomLevel1_Rev.XRefConfigurationID) 
                           AND (qry_BomLevel1_Desc.ChildRevNr = qry_BomLevel1_Rev.ChildRevNr) 
                           AND (qry_BomLevel1_Desc.ChildID = qry_BomLevel1_Rev.ChildID)) 
     LEFT JOIN 
         qry_BomLevel1_Comment ON (qry_BomLevel1_Rev.XRefConfigurationID = qry_BomLevel1_Comment.XRefConfigurationID) 
                               AND (qry_BomLevel1_Rev.ChildRevNr = qry_BomLevel1_Comment.ChildRevNr) 
                               AND (qry_BomLevel1_Rev.ChildID = qry_BomLevel1_Comment.ChildID)) 
          ON (qry_BomLevel1_PartNo.XRefConfigurationID = qry_BomLevel1_Desc.XRefConfigurationID) 
          AND (qry_BomLevel1_PartNo.ChildRevNr = qry_BomLevel1_Desc.ChildRevNr) 
          AND (qry_BomLevel1_PartNo.ChildID = qry_BomLevel1_Desc.ChildID)) --<--Add Closing paren here

    UNION ALL

    (SELECT --<--Add Opening paren here
        qry_BomLevel2_PartNo.Type, qry_BomLevel2_PartNo.PartNo, 
        qry_BomLevel2_Desc.Description, qry_BomLevel2_Rev.Revision, 
        qry_BomLevel2_PartNo.Qty, qry_BomLevel2_Comment.Comment
   FROM --<--Delete Opening paren here
        qry_BomLevel2_PartNo 
   LEFT JOIN 
       ((qry_BomLevel2_Desc 
   LEFT JOIN 
       qry_BomLevel2_Rev ON (qry_BomLevel2_Desc.ChildID = qry_BomLevel2_Rev.ChildID) 
                         AND (qry_BomLevel2_Desc.ChildRevNr = qry_BomLevel2_Rev.ChildRevNr) 
                         AND (qry_BomLevel2_Desc.XRefConfigurationID = qry_BomLevel2_Rev.XRefConfigurationID)) 
   LEFT JOIN 
       qry_BomLevel2_Comment ON (qry_BomLevel2_Rev.ChildID = qry_BomLevel2_Comment.ChildID) 
                             AND (qry_BomLevel2_Rev.ChildRevNr = qry_BomLevel2_Comment.ChildRevNr)
                             AND (qry_BomLevel2_Rev.XRefConfigurationID = qry_BomLevel2_Comment.XRefConfigurationID)) 
         ON (qry_BomLevel2_PartNo.XRefConfigurationID = qry_BomLevel2_Desc.XRefConfigurationID) 
         AND (qry_BomLevel2_PartNo.ChildRevNr = qry_BomLevel2_Desc.ChildRevNr)  
         AND (qry_BomLevel2_PartNo.ChildID = qry_BomLevel2_Desc.ChildID)
)

I went and got myself Notepad++ and that tool is going to be very useful like you suggested. I tried what you suggested and got the same error but did some playing with parenthesis and did get it to work. How do you post code properly here? Ill post what worked in the end. — Paul, Jul 15 '19 at 18:03
The easiest way is to put a row with three back-ticks above and below your code block. (The key in the upper left corner of the keyboard.) If you click the `edit` button on my answer, it'll show you the raw typing with the back-tick rows. A single back tick around a word or phrase grays it out, like it did just there with "edit". — Eric Brandt, Jul 15 '19 at 18:08
Another way, which I see marc_s used on your question, is to indent all the rows four spaces. — Eric Brandt, Jul 15 '19 at 18:10

Getting a Syntax error in FROM clause with UNION ALL query

1 Answers1