Syntax error in Access SQL View

Question

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

  ...
;

Access can only join two tables at the time. You need lots of parentheses. — jarlh, Jun 29 '16 at 14:53

Gord Thompson · Answer 1 · 2016-06-29T18:06:49.520

2

The [Access SQL] syntax requires randomly adding parentheses

It's not random. (I assume that you are misusing the word "random" in much the same way that people misuse the word "literally".)

Access SQL usually requires parentheses when multiple JOIN conditions are involved. That is, it tends to complain about constructs like this:

SELECT ...
FROM 
    tbl1 
    INNER JOIN tbl2 ON tbl1.fld = tbl2.fld1
    INNER JOIN tbl3 ON tbl2.fld = tbl3.fld2

Instead, it needs parentheses around one of the (complete) INNER JOINs. When I'm writing Access SQL by hand I prefer to use indentation like this

SELECT ...
FROM 
    (
        tbl1 
        INNER JOIN 
        tbl2 
        ON tbl1.fld = tbl2.fld1
    )
    INNER JOIN 
    tbl3 
    ON tbl2.fld = tbl3.fld2

Second example:

Instead of

SELECT ...
FROM 
    tbl1 
    INNER JOIN tbl2 ON tbl1.fld = tbl2.fld1
    INNER JOIN tbl3 ON tbl2.fld = tbl3.fld2
    INNER JOIN tbl4 ON tbl3.fld = tbl4.fld3

it would be more like

SELECT ...
FROM 
    (
        (
            tbl1 
            INNER JOIN 
            tbl2 
            ON tbl1.fld = tbl2.fld1
        )
        INNER JOIN 
        tbl3 
        ON tbl2.fld = tbl3.fld2
    )
    INNER JOIN 
    tbl4 
    ON tbl3.fld = tbl4.fld3

edited Jun 29 '16 at 18:06

answered Jun 29 '16 at 16:54

Gord Thompson

116,920
32
215
418

What if I have 2 `INNER JOIN` and a `LEFT JOIN`? – whatwhatwhat Jun 29 '16 at 17:02
Then you just put one pair of parentheses around the JOIN you want to be performed first, and that result will be JOINed to the third table. – Gord Thompson Jun 29 '16 at 17:33
I'm a bit confused. Do you mean `FROM (tbl1 INNER JOIN tbl2 ON ... INNER JOIN tbl3) ON ... LEFT JOIN tbl4 ON ...` or do you mean `FROM ((tbl1 INNER JOIN tbl2 ON ...) INNER JOIN tbl3) ON ... LEFT JOIN tbl4 ON ...` ? – whatwhatwhat Jun 29 '16 at 17:43
I updated my question with simpler code in order to better show what parentheses I'm currently using. – whatwhatwhat Jun 29 '16 at 17:45
1

Sorry, I misread your original comment. I'll update my answer soon. – Gord Thompson Jun 29 '16 at 18:00

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

FULL OUTER JOIN is not supported in Access SQL.

For workarounds, see e.g. How do I write a full outer join query in access

or https://stackoverflow.com/search?tab=relevance&q=%5bms-access%5d%20FULL%20OUTER%20JOIN :p

edited May 23 '17 at 11:44

Community

1
1

answered Jun 29 '16 at 14:54

Andre

26,751
7
36
80

Is there an Access equivalent of joining 2 tables on one field and returning the rows where the fields don't match? – whatwhatwhat Jun 29 '16 at 14:56

Syntax error in Access SQL View

2 Answers2