Nested Left Joins in Access

[DirtyB]

I have a query here that seems as though it should work, but I know I have to do some funky parentheses in Access or something to get joins to work correctly. I have several nested left joins and Access does not like my syntax....any suggestions?

SELECT CLASSES.ID
FROM CLASSES
left JOIN COURSES
left JOIN MSTV
left JOIN MSHEADER
ON (MSTV.MsSection = MSHEADER.MsSection)
AND (MSTV.MsCrsNumber =MSHEADER.MsCrsNumber)
AND (MSTV.MsYear = MSHEADER.MsCrsYear)
AND (AAA_ALLMSTV.MsSchool=MSHEADER.MsCrsSchool)
ON (COURSES.CourseNumber = MSTV.MsCrsNumber)
AND (COURSES.CourseYear = MSTV.MsYear)
AND (COURSES.CourseSchool = MSTV.MsSchool)
ON (CLASSES.Crs = COURSES.CourseNumber)
AND (CLASSES.Year = COURSES.CourseYear)
AND (CLASSES.School = COURSES.CourseSchool)
WHERE CLASSES.ID in (select old_student_number from ESIS_IDS);

 

[RickSpr]

Put a left parenthesis after each of the first two LEFT JOIN keywords, and a right parenthesis before each of the last two ON keywords. I think that will do it. Rick Sprague

 

[DirtyB]

That doesn't seem to work. I've put the parentheses where you said, and I also tried them before the first two left joins and after the last two on's..that didn't work either.

 

[RickSpr]

I overlooked that you have used the column AAA_ALLMSTV.MsSchool, but you don't have such a table. When I changed that to MSTV.MsSchool and put the parentheses in where I said, it was syntactically correct. (I built tables with these names containing the referenced columns, and was able to execute the query.)

Rick Sprague