Solved

SQL query

Posted on 2004-04-22
4
338 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:arron_cooper
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
jlaborde earned 100 total points
ID: 10890765
Aaron,

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.

jlaborde
0
 

Author Comment

by:arron_cooper
ID: 10897322
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?
0
 
LVL 3

Expert Comment

by:jlaborde
ID: 10900658
Arron,

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.

jlaborde
0
 

Author Comment

by:arron_cooper
ID: 10906946
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

Arron
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question