MS Access: Syntax error in FROM Clause

Question

I'm getting a syntax error pointing to my FROM clause, but I can't figure it out. The goal here is to use subqueries to filter and return some information before joining.

What I've tried: This question on SO suggested that I surround the 2 LEFT JOINS with parentheses. I did so, but it didn't help. Below is the result, and the error I get is in the title.

It may or may not be important to note that before doing the parentheses around both JOINS, I was getting the following error: Syntax error (missing operator in query expression 'tbl_HersheySAPInventory.item = maxdatejob.item LEFT JOIN (SELECT * FROM dbo_Item_by_Location_LP_All WHERE qty_on_hand >'.

SELECT 

    tbl_HersheySAPInventory.ID, 
    tbl_HersheySAPInventory.item, 
    dbo_job.job_date, 
    dbo_job.job, 
    dbo_job.suffix,
    Sum(comptable.qty_on_hand) AS SumOfqty_on_hand,
    comptable.whse,
    comptable.product_code

FROM 

    tbl_HersheySAPInventory 

    (LEFT JOIN 

        (SELECT 

            dbo_job.job_date, 
            dbo_job.job, 
            dbo_job.suffix, 
            dbo_job.item AS FG,
            dbo_jobmatl.item

        FROM 

            dbo_job 
            INNER JOIN dbo_jobmatl ON dbo_job.job = dbo_jobmatl.job AND dbo_job.suffix = dbo_jobmatl.suffix 

        WHERE

            dbo_job.item Not Like "Indirect" AND
            dbo_job.job Not Like "C0*" AND
            dbo_job.job Not Like "*R0*"

        ) AS maxdatejob 

    ON tbl_HersheySAPInventory.item = maxdatejob.item)

    (LEFT JOIN

        (SELECT * FROM dbo_Item_by_Location_LP_All WHERE qty_on_hand > 0 AND whse = Forms!MainForm!Combo353)  AS comptable

    ON comptable.item = tbl_HersheySAPInventory.item)


ORDER BY 

    tbl_HersheySAPInventory.item, 
    dbo_job.job_date;

Answer 1

You may have a problem with your parentheses. I have similar issues writing SQL into Access - there seem to be some strange requirements wrapping parens around JOINS and WHERE clauses.

The pseudocode that I recommend trying is:

FROM 
(
(
Table A
)
LEFT JOIN
  (
  Subquery1
  )
)
LEFT JOIN
  (
  Subquery2
  )

I've taken a stab at updating below. Give this a try and let me know:

SELECT 

    tbl_HersheySAPInventory.ID, 
    tbl_HersheySAPInventory.item, 
    dbo_job.job_date, 
    dbo_job.job, 
    dbo_job.suffix,
    comptable.whse,
    comptable.product_code,
    Sum(comptable.qty_on_hand) AS SumOfqty_on_hand    
FROM 
(
(
    tbl_HersheySAPInventory 



    LEFT JOIN 

        (SELECT 

            dbo_job.job_date, 
            dbo_job.job, 
            dbo_job.suffix, 
            dbo_job.item AS FG,
            dbo_jobmatl.item

        FROM 

            dbo_job 
            INNER JOIN dbo_jobmatl ON dbo_job.job = dbo_jobmatl.job AND dbo_job.suffix = dbo_jobmatl.suffix 

        WHERE

            dbo_job.item Not Like "Indirect" AND
            dbo_job.job Not Like "C0*" AND
            dbo_job.job Not Like "*R0*"

        ) AS maxdatejob 

    ON tbl_HersheySAPInventory.item = maxdatejob.item
    )
    )

    LEFT JOIN

        (SELECT * FROM dbo_Item_by_Location_LP_All WHERE qty_on_hand > 0 AND whse = Forms!MainForm!Combo353)  AS comptable

    ON comptable.item = tbl_HersheySAPInventory.item

GROUP BY

    tbl_HersheySAPInventory.ID, 
    tbl_HersheySAPInventory.item, 
    dbo_job.job_date, 
    dbo_job.job, 
    dbo_job.suffix,
    comptable.whse,
    comptable.product_code   


ORDER BY 

    tbl_HersheySAPInventory.item, 
    dbo_job.job_date;