Cannot figure out Access SQL Syntax Error

Question

I am trying to write this query in Microsoft Access SQL and for the life of me I cannot figure out the syntax error in the code.

SELECT base.study_group, base.NPI,inv.log_date AS invite_date, rec.log_date as
recieved_date, rej.log_date as reject_date
FROM CODAAC_master AS base
LEFT JOIN Pre_Log AS inv ON base.NPI = inv.NPI AND inv.tracking_event='INVA'
LEFT JOIN Pre_Log AS rec ON base.NPI = rec.NPI AND rec.tracking_event='RECA'
LEFT JOIN Pre_Log AS rej ON base.NPI = rej.NPI AND rej.tracking_event='REJA'
WHERE base.study_year = '2013'
ORDER BY base.study_group, base.NPI;

The error I get back is:

"Syntax error (missing operator) in query expression"

Use the query design window. You are short of parentheses. Access needs them on joins. — Fionnuala
– Fionnuala, Commented Mar 29, 2013 at 14:36

Taryn · Accepted Answer · 2013-03-29 14:36:47Z

3

MS Access requires parentheses around multiple joins:

SELECT base.study_group, 
  base.NPI,
  inv.log_date AS invite_date, 
  rec.log_date as recieved_date, 
  rej.log_date as reject_date
FROM ((CODAAC_master AS base
LEFT JOIN Pre_Log AS inv 
  ON base.NPI = inv.NPI AND inv.tracking_event='INVA')
LEFT JOIN Pre_Log AS rec 
  ON base.NPI = rec.NPI AND rec.tracking_event='RECA')
LEFT JOIN Pre_Log AS rej 
  ON base.NPI = rej.NPI AND rej.tracking_event='REJA'
WHERE base.study_year = '2013'
ORDER BY base.study_group, base.NPI;

answered Mar 29, 2013 at 14:36

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-03-29 14:57:55Z

3

You need to add parenthesis between the joins as it is essential on MS Access. (This is optional on other RDBMS)

SELECT  base.study_group, 
        base.NPI,
        inv.log_date AS invite_date, 
        rec.log_date as recieved_date, 
        rej.log_date as reject_date
FROM  ((CODAAC_master AS base LEFT JOIN Pre_Log AS inv 
            ON base.NPI = inv.NPI AND inv.tracking_event='INVA')
        LEFT JOIN Pre_Log AS rec 
            ON base.NPI = rec.NPI AND rec.tracking_event='RECA')
        LEFT JOIN Pre_Log AS rej 
            ON base.NPI = rej.NPI AND rej.tracking_event='REJA'
WHERE base.study_year = '2013'
ORDER BY base.study_group, base.NPI;

Extra Source: Multiple LEFT JOINs in MS Access

edited Mar 29, 2013 at 14:57

answered Mar 29, 2013 at 14:36

John Woo

265k70 gold badges509 silver badges500 bronze badges

3 Comments

Sublimix Over a year ago

Thank you for your help. I added the parenthesis but now it is saying that the JOIN expression is not supported.

John Woo Over a year ago

what version of ms access you are using/

John Woo Over a year ago

your question is the on this MS Access 2007 Multiple Left Join, try to execute the updated statement

Collectives™ on Stack Overflow

Cannot figure out Access SQL Syntax Error

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related