Solved

SQL query

Posted on 2004-04-22
4
342 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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

724 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