MS Access: SQL视图错误

Question

当将原始SQL查询粘贴到Access的SQL视图中时，我会收到一个错误。我知道Access语法有点特别，但我不知道它要求什么。错误是：Syntax error (missing operator) in query expression '(jobmatl.suffix = job.suffix) AND (job.job = jobmatl.job) INNER ...................... AS ibl ON jobmatl.item = ibl.item AND job.whse = ibl.whse。这个错误在我写的东西之间提到了所有的东西。

SELECT 

    job.job, 
    job.suffix, 
    job.job_date, 
    job.item AS FG, 
    jobmatl.item,
    job.whse,
    ibl.sumofqtyonhand,
    ibl.whse

FROM 

    job 
    INNER JOIN jobmatl ON (jobmatl.suffix = job.suffix) AND (job.job = jobmatl.job)
    INNER JOIN (

        (SELECT

            i.item,
            SUM(i.qty_on_hand) AS sumofqtyonhand,
            i.whse

        FROM

            Item_by_Location_LP_ALL AS i

        WHERE

            i.hold_flag != 1

        GROUP BY

            i.item,
            i.whse

        )) AS ibl ON jobmatl.item = ibl.item AND job.whse = ibl.whse

WHERE 

    (((job.job_date)=Date()-(DatePart("w",Date(),2,1)-1)));

Answer 1

对于MS Access，FROM应该如下所示：

FROM (job INNER JOIN
      jobmatl
      ON jobmatl.suffix = job.suffix AND job.job = jobmatl.job
     ) INNER JOIN 
     (SELECT i.item, SUM(i.qty_on_hand) AS sumofqtyonhand, i.whse
      FROM Item_by_Location_LP_ALL AS i
      WHERE i.hold_flag <> 1
      GROUP BY i.item, i.whse
     ) AS ibl
     ON jobmatl.item = ibl.item AND job.whse = ibl.whse;

对于每个JOIN，MS访问都需要额外的括号。此外，你必须达到括号的水平--我不知道这是否被允许。