sql - MS Access : SQL View error

Question

将原始SQL查询粘贴到Access的SQL View 时出现错误。我知道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;

MS Access需要为每个 JOIN附加括号。另外，您必须加上括号的水平-我不知道是否允许这样做。