Is the join expression not supported by MS Access?

Question

Can anyone explain to me what is wrong with my query?

SELECT T2.TIPOPRODUTO
    , T2.PRODUTO
    , T1.ESPESSURA
    , '' AS LARGURA
    , '' AS COMPRIMENTO
    , '' AS [ACABAM REVEST]
    , '' AS [ESPECIF QUALIDADE]
    , T1.CÓDIGORASTREABILIDADE
    , T3.DATA
    , T4.NOMEFANTASIA
    , T7.NOME
    , T5.DT_INICIO_RESERVA
    , T1.PESO
    , T5.DT_FIM_RESERVA
    , '' AS DESTINO
    , T3.OBSERVAÇÃO
    , '' AS [CUSTO TOTAL]
FROM ([TABELA DE PRODUTOS/ESTOQUE] LEFT OUTER JOIN [TABELA DE PRODUTOS] ON ([TABELA DE PRODUTOS/ESTOQUE].PRODUTO=[TABELA DE PRODUTOS].ID))
, [TABELA DE PRODUTOS/ESTOQUE ] AS T1
    , [TABELA DE PRODUTOS] AS T2
    , [TABELA DE MOVIMENTAÇÃO DE ESTOQUE] AS T3
    , [TABELA DE FORNECEDORES] AS T4
    , RESERVAS_PRODUTOS_ESTOQUE AS T5
, [TABELA DE MOVIMENTAÇÃO DE PRODUTOS/ESTOQUE] AS T6
    , [TABELA DE USUÁRIOS] AS T7
    , [TABELA DE PEDIDOS DE COMPRA] AS T8
WHERE (((T1.Produto)=[T2].[ID]) 
    AND ((T1.ID)=[T5].[ID_PRODUTO_ESTOQUE]) 
    AND ((T5.id_vendedor)=[T7].[ID]) 
    AND ((T3.ID)=[T6].[ID]) 
    AND ((T2.ID)=[T6].[PRODUTO]) 
    AND ((T4.ID)=[T8].[FORNECEDOR]) 
    AND ((T8.Comprador)=[T7].[ID]));

My best guess is it fails on this line:

([TABELA DE PRODUTOS/ESTOQUE] LEFT OUTER JOIN [TABELA DE PRODUTOS] ON ([TABELA DE PRODUTOS/ESTOQUE].PRODUTO=[TABELA DE PRODUTOS].ID))

Answer 1

You are mixing a join statement with "classical joins" (a comma separated list of tables with conditions in the where statement), which I believe is not allowed.

Change the query to use only join statements. In Access you have to pair the joins using parentheses, in this manner:

from (((t1 join t2 on ...) join t3 on ...) join t4 on ...)

Answer 2

I have not been able to find a way to join 3 or more tables using outer join in MSAccess. It works in MSSQL, but not Access. I don't believe it is possible. I have tables A, B, C, D and E. I need to join the tables like so:

• A left outer join B
• A left outer join C
• A inner join D
• B inner join E

Access won't let you using conventional joins in the where clause when you use LEFT/RIGHT/INNER JOINS in the FROM clause. It is probably intentional to get you to buy more expensive software. You may be able to use a pass-through query, but I don't know how to do that yet. The most tables I can join are 3 with outer joins like this:

FROM (A left join B on A.b = B.b) left join C on A.c = C.c