syntax error when trying to join in Access

Question

My database includes several lookup tables (shown as pulldown menus on the UI form).

For example,

customer_data - customer demographic info.

lookup_car - stores car descriptions (Pinto, Vega, Reliant Robin, Mustang, Corvette)

junction_car_customer - joins a customer with one or more cars

Customer Jeremy Clarkson (cust_id: 1) owns three cars. The dropdown for his record shows:

Pinto (car_id=100)
Reliant Robin (car_id=101)
Vega (car_id=102)

The junction_car_customer data looks like this:

cust_id    car_id
1          100
1          101
1          102

I'm trying to return a row showing the customer name and the models owned.

Here's my query:

SELECT 
 cd.cust_id,
 cd.name_first,
 cd.name_last,
 jcc.car_id,
 lc.car_desc
FROM
 customer_data AS cd
 LEFT JOIN ju_cust_car AS jcc ON jcc.cust_id = cd.cust_id
 LEFT JOIN lookup_cars AS lc ON lc.car_id = jcc.car_id

ORDER BY 
 cd.name_last

I'm getting:

Syntax error (missing operator) in query expression 'jcc.cust_id = cd.cust_id LEFT JOIN lookup_cars AS lc ON lc.car_id = jcc.car_id'

What is causing this error?

Answer 1

Access requires parenthesis for more than one join. For example:

select * 
from ((Table1 as t1)
left join Table2 as t2 on t1.id = t2.id)
left join Table3 as t3 on t1.id = t3.id

Answer 2

Access is a bit fussy about LEFT/RIGHT JOINs and parentheses. Try this

SELECT 
    cd.cust_id,
    cd.name_first,
    cd.name_last,
    jcc.car_id,
    lc.car_desc
FROM
    (
        customer_data AS cd
        LEFT JOIN 
        ju_cust_car AS jcc 
            ON jcc.cust_id = cd.cust_id
    )
    LEFT JOIN lookup_cars AS lc 
        ON lc.car_id = jcc.car_id
ORDER BY 
 cd.name_last