I always have trouble with Access SQL. The syntax requires randomly adding parentheses or moving things around that are different from SQL Server. Does anyone know what the syntax error might be here? The error is focused around the FROM clause. I know the other clauses are ok, but I left them in for reference.
Update: Simplified the code a lot more. I'm mainly concerned about the 3 FROM clauses.
SELECT
...
FROM
(((maintable
INNER JOIN othertable ON (maintable.A = othertable.A) AND (maintable.B = othertable.B))
INNER JOIN
(SELECT
...
FROM
(thirdtable)
INNER JOIN fourthtable ON thirdtable.blah = fourthtable.blah
WHERE
fourthtable.something <> 1
GROUP BY
...
) AS innerselect1 ON (othertable.item = innerselect1.item) AND (othertable.whse = innerselect1.whse)) AS table1)
JOIN (
SELECT ...
FROM (othertable INNER JOIN maintable ON othertable.item = maintable.item)
GROUP BY ...
HAVING ...
) AS table2 ON table1.item = table2.item
WHERE
...
GROUP BY
...
HAVING
...
UNION ALL
SELECT
...
FROM
(((maintable
INNER JOIN othertable ON (maintable.A = othertable.A) AND (maintable.B = othertable.B))
INNER JOIN
(SELECT
...
FROM
(thirdtable)
INNER JOIN fourthtable ON thirdtable.blah = fourthtable.blah
WHERE
fourthtable.something <> 1
GROUP BY
...
) AS innerselect1 ON (othertable.item = innerselect1.item) AND (othertable.whse = innerselect1.whse)) AS table1)
LEFT JOIN (
SELECT ...
FROM (othertable INNER JOIN maintable ON othertable.item = maintable.item)
GROUP BY ...
HAVING ...
) AS table2 ON table1.item = table2.item
WHERE
table2.item IS NULL
GROUP BY
...
HAVING
...
UNION ALL
SELECT
...
FROM
(((maintable
INNER JOIN othertable ON (maintable.A = othertable.A) AND (maintable.B = othertable.B))
INNER JOIN
(SELECT
...
FROM
(thirdtable)
INNER JOIN fourthtable ON thirdtable.blah = fourthtable.blah
WHERE
fourthtable.something <> 1
GROUP BY
...
) AS innerselect1 ON (othertable.item = innerselect1.item) AND (othertable.whse = innerselect1.whse)) AS table1)
RIGHT JOIN (
SELECT ...
FROM (othertable INNER JOIN maintable ON othertable.item = maintable.item)
GROUP BY ...
HAVING ...
) AS table2 ON table1.item = table2.item
WHERE
table1.item IS NULL
GROUP BY
...
HAVING
...
;
