Left Join On And clause not supported

I’ve looked into various posts (this one, that one and this other one) and thought I got the answer.

After a LEFT JOIN I may add an ON [condition] AND [other condition] (I’ve also tried WHERE). But computer says no. Access keeps saying the join expression is not supported.

Consider the student_records table below:

STUDENTCODE  | SEMESTERINDEX
12345        | 20112
12345        | 20113
12345        | 20121
67890        | 0
67890        | 20111
67890        | 20112

I want to find the minimum SEMESTERINDEX for each student from my students table, that’s above 20001. (Records below may be erroneous and the 0 and 1 SEMESTERINDEX is used for transferred credits.)

I’m using access so there are VBA functions inside the SQL. There’s several more tables I’m joining too, I’m quoting the whole query.

SELECT students.STUDENTCODE, prefixes.PREFIXNAMEENG, 
students.STUDENTNAMEENG, students.STUDENTSURNAMEENG, levels.level_name, programs.PROGRAMNAMEENG, calendars.calendar_load, 
MAX(student_records.SEMESTERINDEX) AS latest_semester, MIN(student_records.SEMESTERINDEX) AS intake_semester,
FROM student_records LEFT JOIN (
    (
        (
            (
                (students LEFT JOIN prefixes ON students.PREFIXID = prefixes.PREFIXID) 
                LEFT JOIN levels ON students.LEVELID = levels.level_id) 
            LEFT JOIN programs ON students.PROGRAMID = programs.PROGRAMID) 
        LEFT JOIN calendar_conversion ON students.SCHEDULEGROUPID = calendar_conversion.schedule_id) 
    LEFT JOIN calendars ON calendar_conversion.calendar_id = calendars.calendar_id) ON student_records.STUDENTCODE = students.STUDENTCODE AND student_records.SEMESTERINDEX> 2001
GROUP BY students.STUDENTCODE, prefixes.PREFIXNAMEENG, students.STUDENTNAMEENG, students.STUDENTSURNAMEENG, levels.level_name, programs.PROGRAMNAMEENG, calendars.calendar_load;

So did I misplace the AND student_records.SEMESTERINDEX > 2001?

Answer

oh my save me from these parenthesis and crazy indenting.

Here is how you do it. All the parenthesis don’t matter in SQL

SELECT 
  students.STUDENTCODE, 
  prefixes.PREFIXNAMEENG, 
  students.STUDENTNAMEENG, 
  students.STUDENTSURNAMEENG, 
  levels.level_name, 
  programs.PROGRAMNAMEENG, 
  calendars.calendar_load, 
  minmax.latest_semester, 
  minmax.intake_semester,
FROM student_records 
LEFT JOIN (
   SELECT 
     studentcode,
     MAX(student_records.SEMESTERINDEX) AS latest_semester, 
     MIN(student_records.SEMESTERINDEX) AS intake_semester
   FROM students
   WHERE students.STUDENTCODE > 2001
   GROUP BY studentcode
) as MinMax ON student_records.STUDENTCODE = minmax.STUDENTCODE
LEFT JOIN students ON student_records.STUDENTCODE = students.STUDENTCODE 
LEFT JOIN prefixes ON students.PREFIXID = prefixes.PREFIXID
LEFT JOIN levels ON students.LEVELID = levels.level_id
LEFT JOIN programs ON students.PROGRAMID = programs.PROGRAMID
LEFT JOIN calendar_conversion ON students.SCHEDULEGROUPID = calendar_conversion.schedule_id
LEFT JOIN calendars ON calendar_conversion.calendar_id = calendars.calendar_id

This is called a sub-query in sql it allows you to perform your grouping on a sub-set and then join that back to the rest of the data.

I think you went wrong thinking there was something about the join that needed a filter — in fact it is the data that you were joining to that needed to be filtered.