Query multiple records

[Chaos Maximus]

Table/Fields



Students/ Student ID, First name, last name....

Tests/ Student ID, Test Name, Test Score, Test Date.



I need to be able to find students based on their record of tests. For example I need to find each student who got above a certain score on 3 or more tests consecutively and within a certain time period.



In one to many relationships I have no idea how to find the one by querying the many related to it. I am new to access and any help would be appreciated greatly.



Chaos

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Chaos Maximus]

sorry, please reply to the top post.

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Guest]

Try something like the following query. In this example it should return all
students with 3 consecutive scores of 65+ this month. I haven't tested it
against any data but the idea behind it is that the first sub-subquery looks
for the last test date for the student before the outer query's current date
and compares this which the value returned by the second query which is
further restricted to scores of 65+. The subquery which counts the rows
where the two sub-subqueries return equal values will return one less than
the count of consecutive rows in the run which satisfy the score criterion,
so 1 is added to the returned value and this then compared with the
requirement of 3 or more consecutive scores of or above 65.

I hope I've got the logic right!

SELECT [First Name], [Last Name]
FROM Students
WHERE
(SELECT COUNT(*) + 1
FROM Tests As T1
WHERE T1.[Student ID] = Students.[Student ID]
AND [Test Date] BETWEEN #08/01/2007# AND #08/31/2007#
AND
(SELECT MAX([Test Date])
FROM Tests As T2
WHERE T2.[Test Date] < T1.[Test Date]
AND T2.[Student ID] = T1.[Student ID]) =
(SELECT MAX([Test Date])
FROM Tests As T3
WHERE T3.[Test Date] < T1.[Test Date]
AND T3.[Student ID]= T1.[Student ID]
AND T3.[Test Score] >= 65)
AND T1.[Test Score] >= 65) >= 3;

Ken Sheridan
Stafford, England