Posted on 2004-04-22
Ok, I'm a bit confused with the SQL of a query, whatever I try I don't seem to be getting it quite right.
I have two queries QryStudentTest and QryStudentTakes (both of which work fine), which contain:-
QryStudentTest: StudentNumber, StudentName, SubjectGroup, Result...(those are the important ones anyway)
QryStudentTakes: StudentNumber, SubjectGroup, SubjectID, TeacherNumber.
And the function CurrentUser() containts the number of the user currently logged on.
I want a query that will select the listed attributes from Qrystudenttest, but only when the studentnumber takes a lesson when the teachernumber = currentuser() AND even then they can only see the students test data for the subjectgroup in which they teach the student.
So an english teacher can see the test results of only the students he teaches, and he can only see their english results.
Any ideas on how to do that? Oh, and if I just add TeacherNumber to QryStudentTest in the design view it goes mad and makes a few thousand results.
Thanks for any help