Slight change in working query causes JOIN error

Question

This has had me scratching my head all day. I have multiple queries where I am trying to join using a calculated field. This first query runs as intended, except MS Access design view doesn't work, which isn't a huge deal since I can still edit in SQL view.

SELECT PM_qryBOM_1.parentid AS LVL0,
       PM_qryBOM_1.childid AS LVL1,
       [PM_qryBOM_1].[parent_part_no] & "/" & [PM_qryBOM_1].[child_part_no] AS BOM_Address,
       PM_qryBOM_1.qty,
       [PM_qryBOM_1].[parentid] & "/" & [PM_qryBOM_1].[childid] AS BOM_ID,
       sol_avgcost.avgcost_material,
       sol_avgcost.avgcost_labor,
       sol_avgcost.avgcost_sub,
       cm_qrypndj_avg.avgofunit_hrs,
       PM_qryBOM_1.parentid  AS Parent_BOM_ID,
       PM_qryBOM_1.parent_part_no AS Parent_BOM_Address,
       pm_qrybom_lvl1_costroll.mult
FROM   (((pm_qrybom AS PM_qryBOM_1
          LEFT JOIN pm_qrybom AS PM_qryBOM_t
                 ON PM_qryBOM_1.parentid = PM_qryBOM_t.childid)
         LEFT JOIN sol_avgcost
                ON PM_qryBOM_1.child_part_no = sol_avgcost.part_no)
        LEFT JOIN cm_qrypndj_avg
               ON PM_qryBOM_1.child_part_no = cm_qrypndj_avg.part_no)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON [PM_qryBOM_1].[parentid] & "/" & [PM_qryBOM_1].[childid] =
                 pm_qrybom_lvl1_costroll.parent_bom_id
WHERE  (( ( PM_qryBOM_t.childid ) IS NULL ))
ORDER  BY [PM_qryBOM_1].[parent_part_no] & "/" & [PM_qryBOM_1].[child_part_no];

I have multiple other queries where I want to JOIN in this manner. So I tried applying this same syntax to my next query, but I get the error

JOIN expression not supported.

 SELECT pm_qrybom_lvl0.lvl0,
       pm_qrybom.childid AS LVL1,
       pm_qrybom.qty,
       [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no] AS BOM_Address,
       [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] AS BOM_ID,
       pm_qrybom_lvl0.bom_address AS Parent_BOM_Address,
       pm_qrybom_lvl0.bom_id AS Parent_BOM_ID,
       sol_avgcost.avgcost_material,
       cm_qrypndj_avg.avgofunit_hrs,
       cm_collections.cpq_material,
       pm_qrybom_lvl1_costroll.mult,
       Iif([cm_collections].[cpq_material] <> 0, [cm_collections].[cpq_material], 
           Iif([mult] = 0, [sol_avgcost].[avgcost_material], [mult])) AS Material_Calc
FROM   ((((pm_qrybom_lvl0
           INNER JOIN pm_qrybom
                   ON pm_qrybom_lvl0.lvl0 = pm_qrybom.parentid)
          LEFT JOIN sol_avgcost
                 ON pm_qrybom.child_part_no = sol_avgcost.part_no)
         LEFT JOIN cm_qrypndj_avg
                ON pm_qrybom.child_part_no = cm_qrypndj_avg.part_no)
        LEFT JOIN cm_collections
               ON pm_qrybom.childid = cm_collections.partid)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] =
                 pm_qrybom_lvl1_costroll.parent_bom_id
ORDER  BY [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no];

The syntax for both queries is the same. Only the reference table names and field names are different. So why can I join with an expression in one query but not the next? I Googled this problem and some suggested to put brackets around everything after ON in the JOIN statement. I tried that and it did not resolve the problem.

Any suggestions would be appreciated as I am absolutely stuck on this one.

Syntax is not exactly the same with different tables and fields. You have an `INNER JOIN` in latter. Access queries are very difficult to script on its own with multiple `JOIN`. Build as much as possible in query design which I believe faults at the `ON` clause with `/`, then extend in SQL view. — Parfait, Dec 22 '17 at 20:51
My suggestion would be to remove the nested joins and refactor them to be in line. Much easier to read. I have no idea why Access likes to do it this way, but it doesn't need to be like this. I will write up an answer with it refactored that way. — Alan, Dec 23 '17 at 03:19
When you switch from SQL view to datasheet view, Access shows the error and should then highlight a part of the SQL. Which part is that? — Andre, Dec 23 '17 at 07:39

score 0 · Answer 1 · answered Dec 23 '17 at 12:07

It's the concatenation at the end of the JOIN statement that's not playing well with the INNER JOIN you tried to use. Try it with a LEFT JOIN. Then, in the WHERE statement, remove the ones that had no match on the right by excluding NULLs:

SELECT pm_qrybom_lvl0.lvl0,
       pm_qrybom.childid AS LVL1,
       pm_qrybom.qty,
       [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no] AS BOM_Address,
       [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] AS BOM_ID,
       pm_qrybom_lvl0.bom_address AS Parent_BOM_Address,
       pm_qrybom_lvl0.bom_id AS Parent_BOM_ID,
       sol_avgcost.avgcost_material,
       cm_qrypndj_avg.avgofunit_hrs,
       cm_collections.cpq_material,
       pm_qrybom_lvl1_costroll.mult,
       Iif([cm_collections].[cpq_material] <> 0, [cm_collections].[cpq_material], 
           Iif([mult] = 0, [sol_avgcost].[avgcost_material], [mult])) AS Material_Calc
FROM   ((((pm_qrybom_lvl0
           LEFT JOIN pm_qrybom
                   ON pm_qrybom_lvl0.lvl0 = pm_qrybom.parentid)
          LEFT JOIN sol_avgcost
                 ON pm_qrybom.child_part_no = sol_avgcost.part_no)
         LEFT JOIN cm_qrypndj_avg
                ON pm_qrybom.child_part_no = cm_qrypndj_avg.part_no)
        LEFT JOIN cm_collections
               ON pm_qrybom.childid = cm_collections.partid)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] =
                 pm_qrybom_lvl1_costroll.parent_bom_id
       WHERE pm_qrybom.parentid IS NOT NULL
ORDER  BY [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no];

If that doesn't give you the results you want, you could also try nesting a select statement that gives you the concatenation in the from statement.

Something like:

SELECT iq.lvl0,
       iq.childid AS LVL1,
       iq.qty,
       iq.BOM_Address,
       iq.BOM_ID,
       iq.Parent_BOM_Address,
       iq.Parent_BOM_ID,
       sol_avgcost.avgcost_material,
       cm_qrypndj_avg.avgofunit_hrs,
       cm_collections.cpq_material,
       pm_qrybom_lvl1_costroll.mult,
       Iif([cm_collections].[cpq_material] <> 0, [cm_collections].[cpq_material], 
           Iif([mult] = 0, [sol_avgcost].[avgcost_material], [mult])) AS Material_Calc
FROM ((((SELECT pm_qrybom_lvl0.lvl0, pm_qrybom.childid, pm_qrybom.qty, 
        [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no] AS BOM_Address,
        [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] AS bom_id,
        pm_qrybom_lvl0.bom_address AS Parent_BOM_Address,
        pm_qrybom_lvl0.bom_id AS Parent_BOM_ID
        FROM pm_qrybom_lvl0 LEFT JOIN pm_qrybom
                   ON pm_qrybom_lvl0.lvl0 = pm_qrybom.parentid
        WHERE pm_qrybom.parentid IS NOT NULL

            ) AS iq
          LEFT JOIN sol_avgcost
                 ON iq.child_part_no = sol_avgcost.part_no)
         LEFT JOIN cm_qrypndj_avg
                ON iq.child_part_no = cm_qrypndj_avg.part_no)
        LEFT JOIN cm_collections
               ON iq.childid = cm_collections.partid)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON iq.bom_id =
                 pm_qrybom_lvl1_costroll.parent_bom_id

Good luck.

Thanks for all the suggestions. I’ll give these a try!! – jhalf2008 Dec 23 '17 at 20:14 — jhalf2008, Dec 23 '17 at 20:14

Slight change in working query causes JOIN error

1 Answers1