CODEWITHSUNDEEP
sqlms-access
Sublimix
Sublimix

Reputation: 41

Cannot figure out Access SQL Syntax Error

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"

Upvotes: 2

Views: 90

Answers (2)

John Woo
John Woo

Reputation: 263683

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;

Upvotes: 3

Taryn
Taryn

Reputation: 247630

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;

Upvotes: 3

Related Questions