Search results for query: *

Sorry; when i tried the above it doesnt give the right tutor codes because i need the constraints in (being in the same faculty, having space etc) When i tried the above with HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblTutor.[TU_FAC_NO] = " & fac & " AND...

Yes it is odd, as soon as i add in the tblPreference.[TU_COURSE] <> '" & course & "' criteria it starts to only take tutors who have entries in that table

Yes that shows the right tutors for most apart from a tutor who has one of the courses in tblPreference

Im getting the same results as when i use an inner join, when i know i should be getting a different tutor that does not have an entry in tblPreferences

Sorry i hope you didnt think i was being rude, i'd already tried using a left join and got the same results. Maybe i didnt explain properly what im trying to do. The tutor im trying to select can either have no entries in tblPreference or some entries in tblPreference (as long as their...

Sorry im not following ck1999, you say i should always use an inner join? course is a recordset item (course = rs("STU_COURSE_CODE")) so having <> "course" doesnt work

Some tutors do have an entry in this table and some dont, is there a single piece of code i can use or do i have to do some kind of search and then a certain type of join depending on the result of the search?

Ive a question relating to joins, in the code below im trying to find a tutor, who has free space, is in a certain faculty, doesnt have TU_ONLY and who doesnt have a preference for a certain course. Basically it works for the first three criteria but it only returns a tutor who has an entry in...

Thanks everyone for all your help it works now

Sorry for the above i meant If Not (rs2 Is Nothing) Then rs.Edit rs!STU_TU_CODE = rs2("TU_CODE") rs.Update Else rs!STU_TU_CODE = " " End If

Ive tried both of those but neither work. Ive tried If Not (rs2 Is Nothing) Then rs!STU_TU_CODE = " " Else rs.Edit rs!STU_TU_CODE = rs2("TU_CODE") rs.Update End If But it still highlights rs!STU_TU_CODE = rs2("TU_CODE") and says...

Ive seen similar posts on this but none of the solutions seems to work for me. Im trying to test if a recordset is null or not. If it is i don't want another recordset to have a value for STU_TU_CODE else i do want it to have a value. I have a few variations, the latest one is below and has an...

Sorry i had tried = Is Not Null Everything is working great now thank you very much, i really appreciate it

Sorry that was a silly mistake, now all the STU_TU_CODEs are the first tutor. This is because im not yet moving those allocated into the permanent students table. Is there a way of saying, if STU_TU_CODE contains something eg: strsql = "Insert into [tblStudents] ( STU_ID, STU_FORENAME...

Im sorry but im still really confused, ive tried going about it a slighly different way by selecting the first tutor with the right requirements and then assigning the student to them (as below) strsql2 = "Select tblWorking.STU_ID, tblWorking.STU_FORENAME, tblWorking.STU_SURNAME...

i tried inserting dbopendynaset but i get the same error?

Another one for you geniuses, Im trying to assign students to tutors until a tutors chamber is full. I have tried the code below but it wont let me update the space a tutor has left in their chamber. Its says "cannot update: database or object is read only". Does anyone know how i can get...

Thank you PHV that worked

I have a query that returns if someone has free space (determined by taking the number of students they have from their chamber size) this works fine if those people have students, but if they dont have students they dont show. How do i incorporate that if they have no students their chamber...

Thanks