CODEWITHSUNDEEP
sqlms-accesssyntax
whatwhatwhat
whatwhatwhat

Reputation: 2266

Syntax error in Access SQL View

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

  ...
;

Upvotes: 0

Views: 68

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123549

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

Upvotes: 2

Andre
Andre

Reputation: 27634

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

Upvotes: 1

Related Questions