Solved

SQL query

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now