Can't Find 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;

Answer 1

seems OK to me, spelling of received shouldn't make a difference coz you are declaring it as the (new) column name for rec.log_date for your resulting table....



I can't run the code of course because i don't have your database, what is your error exactly....?



you could try putting...



using base



as the first statement (just in case you are trying to run the cade against the master DB)

Answer 2

It's hard to tell without knowing the data types of each of the columns you're using in your joins. Access is very picky about this. For instance, dates are encapsulated by "#" instead of ' like every other database.

Try changing your joins. Put the second criteria (" AND inv.tr...") into the WHERE clause instead. It will behave the same way, although it may take a bit longer to process. But, at the least, you can see if that was the issue, because I'm not sure that Access even allows that.

Answer 3

You cannot use the AND operator on a left join. But it seems you're also setting a Where condition in your Left join. Then it seems you're aliasing the same table three times and joining to the same table.



SELECT base.study_group, base.NPI,inv.log_date AS invite_date, inv.log_date as recieved_date, inv.log_date as reject_date

FROM CODAAC_master AS base

LEFT JOIN Pre_Log AS inv ON base.NPI = inv.NPI

WHERE base.study_year = '2013' AND inv.tracking_event In ('INVA', 'RECA', 'REJA')

ORDER BY base.study_group, base.NPI;

Answer 4

I don't know squat about sql's, but I do know that the word 'recieved' is spelled incorrectly. Might make a difference?