Returning only from one table unnecessarily

[beckyr]

Can someone please help me, this has been driving me mad for days (sorry about the title)

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 specific course ('" & course & "')


My code is below and at the moment it is returning only tutors who do have an entry in tblPreference even though i know there are eligible tutors before them 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 & "')"

 

[gemma-the-husky]

can you reproduce this in a query design pane - its much easier if you can build your queries piecemeal with that tool.

Unless you have a sort, of some nature in the SQL, you will get a "random" item matching the query parameters i think, because you are selecting TOP 1

 

[beckyr]

Ive Gemma ive tried query design pane to no avail.

I dont think i am getting a random item matching the query parameters because its selecting tutors in order of their tutor code just from tblPreferences

 

[gemma-the-husky]

what i mean is, by selecting top1, you are only getting 1 item

so when you say you arent getting records with matchnig preferences, it may be because it keeps giving you the same 1 record (thats what i meant by a random 1)

take away the top1, or make it top10 and see what happens