whatwhatwhat whatwhatwhat - 5 months ago 16
SQL Question

MS Access: SQL View error

I'm getting an error when pasting in a raw SQL query into Access's SQL View. I know Access syntax is a bit special but I can't figure out what it's asking for. The error says:

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
. The error mentions everything in between what I've written.

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

The FROM should look like this for MS Access:

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 requires extra parentheses for each JOIN. In addition, you have to levels of parentheses -- and I don't know if that is allowed.

Comments