Reputation: 2276
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)));
Upvotes: 0
Views: 47
Reputation: 1271231
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.
Upvotes: 1