Query to display test if multiple tests have been passed

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

Hi,

I need to devise a query that will diplay student details for students
who have passed tests 1, 2 and 3.

My table(StudentTbl) contains personal data: FirstName (Text Box),
LastName (Text Box) etc and ExamTble contains contains details of
results: TestID(Text Box), Pass (Chech Box), Fail (Check Box) etc.

The tables have a one to many relationship.

I have tried using the criteria for TesdID as 1 And 2 And 3. The query
is able to run and displays no error messages but I get no data
displayed.

Is it possible to use the And Function in a query?

If not can anyone suggest another way to show if students have passed
1, 2 and 3?

Thanks Terry
 
Try something along the lines of

SELECT studentId, FirstName, LastName
FROM StudentTbl INNER JOIN ExamTbl ON StudentTbl.studentId
= ExamTbl.studentId
WHERE ExamTbl.testId IN (1,2,3)
GROUP BY studentId, FirstName, LastName
HAVING COUNT (ExamTbl.*) = 3

This assumes that you have a unique column studentId that
is the primaryKey on the StudentTbl and a ForeignKey on the
ExamTbl.

Hope This Helps
Gerald Stanley MCSD
 
Hi Gerald,

Thanks for taking the time out. I tried your code with slight
variations to comply eith my fielnames as follows:

SELECT StudentID, FirstName, LastName
FROM StudentTbl INNER JOIN ExamTbl ON StudentTbl.studentId
= ExamTbl.studentId
WHERE ExamTbl.ModuleId IN (1,2,3)
GROUP BY StudentID, FirstName, LastName
HAVING COUNT (ExamTbl.*) = 3

I can't run the code as I get two error messages:

1. The first time I run it I get 'Syntax error on expression COUNT
(ExamTbl.*)'

I thought this might be a missing end of statement but even when I put
a semi colon in I get the same message.

2. So I tried to run it without the COUNT and am geting the followin
error:

'The specified field StudentID could refer to more than one table in
the FROM clause of yourSQL statement'

Any Ideas?
Thanks Terry
 
Try
SELECT StudentID, FirstName, LastName
FROM StudentTbl INNER JOIN ExamTbl ON StudentTbl.studentId
= ExamTbl.studentId
WHERE ExamTbl.ModuleId IN (1,2,3)
GROUP BY StudentID, FirstName, LastName
HAVING COUNT (ExamTbl.ModuleId) = 3

Hope This Helps
Gerald Stanley MCSD
 
Hi Gerald,

It works!!!

Appologies for my lapse in etiquet by posting the same question to
another group. Your help has been very much appreciated and has
provided me with a solution.

I had to make a few minor adjustments but that may be because I failed
to inform you that I am using Access 2003....anyway the code is now
working perfectly and now that I know how it works, it will be
invaluable to me in the future, as it is the solution to my problem of
duplication.

This is what I eventually ended up with. It's almost exactly the same
as yours, except for lots of brackets and the last line.

SELECT ExamTbl.StudentID, StudentTbl.FirstName, StudentTbl.LastName
FROM StudentTbl INNER JOIN ExamTbl ON
StudentTbl.StudentID=ExamTbl.StudentID
WHERE (((ExamTbl.ModuleID) In (1,2,3)))
GROUP BY ExamTbl.StudentID, StudentTbl.FirstName, StudentTbl.LastName
HAVING (((Sum(ExamTbl.Pass))=-3));
 
Back
Top