• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

SQL query

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
  • 2
  • 2
1 Solution

You will need to first link the tables together in a meaningful manner.  If you are doing this in a regular query window you will link the two tables on StudentNumber and SubjectGroup, simply drag the fields from one table to the other and a link will be created in your query.  If you are doing this in SQL, your link would be in the JOIN clause

...From qryStudentTest join qryStudentTakes on qryStudentTest.studentNumber = qryStudentTakes.StudentNumber and qryStudentTest.SubjectGroup = qryStudentTakes.SubjectGroup

This will Group the tests by the Student and SubjectGroup.  Next to limit it to the single teacher you will need to add the criteria to the where clause.  In the Access query window you will enter CurrentUser() in  the criteria row under TeacherNumber.  In SQL you will add a Where statement:

...Where qryStudentTakes.TeacherNumber = CurrentUser()

All of this should be done in a new query with the two queries brought in as your data sources.

arron_cooperAuthor Commented:
SELECT StudentNumber, StudentName, SubjectGroup, Result
FROM QryStudentTest INNER JOIN QryStudentTakes ON (QryStudentTest.SubjectGroup=QryStudentTakes.SubjectGroup) AND (QryStudentTest.StudentNumber=QryStudentTakes.StudentNumber)
WHERE (((QryStudentTakes.StaffNumber)=CurrentUser()));

I'm guessing that it's an inner join, as Join alone won't work.  This producted an error saying "The specified field 'Student Number' could refer to more than one table listed in your SQL clause".  Adding QryStudentTakes. in front of each of the selects creates a data mismatch type in criteria expression.

I believe that I've got my table relationships defined correctly, but could it be as an account of that?

You need to define which student takes you want in the select portion of your query.  As for your data mismatch, check the data type of each of the student number fields.  You will probably find that they do not match.  If you correct that it should work for you.

arron_cooperAuthor Commented:
I managed to solve it :)
Here are my SQL statements incase anyone has a similar problem in the future.

SELECT StudentNumber
FROM QryStudentTakes
WHERE StaffNumber=CurrentUser();

This was the first one, which selects the numbers of the students that the logged in staff member teaches.

SELECT QryStudentTest.StudentName, QryStudentTest.TestID, QryStudentTest.TestName, QryStudentTest.SubjectGroup, QryStudentTest.DateTaken, QryStudentTest.Result, QryStudentTest.StudentNumber
FROM QryStudentTest, QryStaffTeaches
WHERE (((QryStudentTest.SubjectGroup)=UserSubject()) And ((QryStudentTest.StudentNumber)=QryStaffTeaches.StudentNumber));

And the second then applied these numbers to studenttest query, to get only the results for the teachers students.
I also created a variable UserSubject() to store the taught subject of the logged in teacher, so this was also applied.  This finally retrieved only results from taught students, and only the results relating to the member of staff, not for other subjects.

Thanks for the answers, helped me look at it slightly differently

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now