6

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Chloe
  • 25,162
  • 40
  • 190
  • 357
  • I hope you find the edit acceptable. This seems to remove the argumentative bits, which might lead you to more downvotes – EFraim Aug 01 '09 at 21:12
  • Perhaps. I've researched it thoroughly and most replies in forums try to offer a useless or incorrect answer. – Chloe Aug 01 '09 at 21:18

2 Answers2

5

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
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Unfortunately, Access documentation specifically says it does not allow an INNER join inside of an OUTER join... I've already tried that and it gives the vague error "Join expression not supported". – Chloe Aug 03 '09 at 07:26
  • 2
    "A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN." http://office.microsoft.com/client/helppreview.aspx?AssetID=HA012314871033&QueryID=QPtFEJVUy&respos=6&rt=2&ns=MSACCESS&lcid=1033&pid=CH100728991033 – Chloe Aug 03 '09 at 07:29
  • You can use a left join between B and E, and add a condition in the where clause to only include rows where there is any data from the E table. That leaves you with a single inner join. – Guffa Aug 03 '09 at 11:36
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.

Cruachan
  • 15,733
  • 5
  • 59
  • 112
  • Access/Jet doesn't have "views. It does have saved queries, which are much like "views," but I don't it's useful to talk about Access using terminology from other databases. – David-W-Fenton Aug 02 '09 at 00:59
  • Well functionally they are the same as view, but technically you're correct and I've modified the answer to use the correct terminology – Cruachan Aug 02 '09 at 08:55
  • @David W. Fenton: "Access/Jet doesn't have views" -- if that were tru then why does it have a CREATE VIEW syntax (http://office.microsoft.com/en-us/access/HP010322231033.aspx) INFORMATION SCHEMA has a VIEWs rowset as supported by the OLE DB Providers for Jet and ACE from Jet 3.51 and earlier. The problem with only using ANSI-89 Query Mode (e.g. DAO) is that you don't see some of the more 'recent' functionality (i.e. 'recent' meaning 'in the last decade'!!) – onedaywhen Aug 02 '09 at 14:22
  • Wow, that seems so messy, bad, and well, evil, it might just be the solution. I'm using Visual Basic/Application Basic so I wonder if it will work in that. I hope it's actually faster than multiple queries inside a loop. I will try, thanks... I think! – Chloe Aug 03 '09 at 07:42
  • Oh it's indeed horrible, although it is what the documentation recommends as a solution to the problem (or was last time I looked) - and it works because as David Fenton points out saved queries in Access are not quite the same as views in a real dbms. One of those areas where it can be not too bad if you take the time to work out a logical approach and naming convention - otherwise is descends into spagetti – Cruachan Aug 03 '09 at 08:50