?
Solved

SQL query

Posted on 2004-04-22
4
Medium Priority
?
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
jlaborde earned 300 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

801 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