Multiple LEFT JOINs in MS Access Database

by
If you are joining related link tables in MS Access database as like SQL Server database, it will work fine for a single link table, but it will not work for more than one join query. The below single join query will work fine in both MS Access and SQL database.

SELECT A.ca, B.cb
FROM TableA AS A LEFT JOIN TableB AS B ON B.id = A.id

Join more than one table in MS Access

If you want to join more than one tables, the below sql like join query will work only in SQl database.

SELECT A.ca, B.cb, C.cc
FROM TableA AS A LEFT JOIN TableB AS B ON B.id = A.id
LEFT JOIN TableC AS C ON C.id = A.id

If you run the above query in MS Access database, you will get the error “Syntax error (missing operator) in query expression“.

To make multiple joins work in MS Access database, we need to enclose join query with parentheses.

SELECT A.ca, B.cb, C.cc
FROM (TableA AS A LEFT JOIN TableB AS B ON B.id = A.id)
LEFT JOIN TableC AS C ON C.id = A.id

Join more than two or more tables in MS Access

Like the above query, we need to add parentheses for every join query to make multiple joins work in MS Access database.

SELECT A.ca, B.cb, C.cc,D.cd
FROM ((TableA AS A LEFT JOIN TableB AS B ON B.id = A.id)
LEFT JOIN TableC AS C ON C.id = A.id)
LEFT JOIN TableD AS D ON D.id = A.id

Related Posts

Advertisement

2 thoughts on “Multiple LEFT JOINs in MS Access Database”

  1. HOW TO PLACE MORE THAN ONE CRITERION ON LEFT JOIN?

    ex.:
    SELECT A.ca, B.cb, C.cc,D.cd
    FROM ((TableA AS A LEFT JOIN TableB AS B ON B.id = A.id)
    LEFT JOIN TableC AS C ON C.id = A.id)
    LEFT JOIN TableD AS D ON D.id = A.id AND D.YEAR = ‘2020’

    features syntax error

    Reply

    • You need to put the multiple conditions into parenthesis, like this:

      …. LEFT JOIN TableD AS D ON (((D. id = A. id AND D.year = 2020)));

      Beware, MS Access removes these parenthesis (one pair) each time you open the SQL statement in design mode. Be sure to use multiple pairs of parenthesis and “top up” soon enough before they run out.

      A query can contain damaged SQL so it can’t be opened anymore, even in design mode. To obtain the SQL statement anyway, use the interaction area and type this:

      ? currentdb.QueryDefs(“queryname”).SQL

      Reply

Leave a Comment