I am trying to find the first TU_CODE that has free space, is in a certain faculty, doesnt have TU_ONLY, and who either does or does not have an entry in tblPreference - but if they do have an entry in tblPreference it cant be for a specify course ('" & course & "')
My code is below and at the moment it is returning only tutors do do have an entry in tblPreference even though i know there are eligible tutors before them (code wise) that do not have an entry in tblPreference - anyone any ideas please!
strsql2 = "SELECT TOP 1 tblTutor.TU_CODE " _
& "FROM (tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
& "left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
& "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_FAC_NO, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE " _
& "HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false AND tblPreference.[TU_COURSE] <> '" & course & "')"