How do I join 4+ tables in Access with a combination inner/outer?

Question

I have not been able to find a way to join 4 or more tables using outer join in MSAccess. It works in SQL Server, 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 use conventional joins in the where clause when you use LEFT/RIGHT/INNER JOINS in the FROM clause. If you do, I get very, very vague errors like "JOIN expression not supported" or "Syntax error (missing operator) in query expression". I 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

Don't say anything about 'outer' keyword not allowed either, because though it is not in the documentation, it does accept it.

Answer 1

In Access you can only join two results at a time. To join more tables you need more parentheses:

from
   (
      (
         (
            A inner join D on D.id = A.id
         )
         left join B on B.id = A.id
      )
      inner join E on E.id = B.id
   )
   left join C on C.id = A.id

Answer 2

A common work around for this is to use saved queries to join your first pair of tables and build upwards using successive queries from there. It's messy, although some forethought as to what future data access paths will be required can help keep things reasonably logical.