Need Help

[Chaos Maximus]

I am new to access and need to know if there is an easy way (or if its even possible) to do the following.

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.

Thanks

Chaos

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

 

[John Spencer]

Possible yes.
Easy No.

The tough part is consecutive tests.

I am assuming that you mean for a student taking a specific test where the
score is above a certain minimum. If we ignore the consecutive part, then
life is much easier.

SELECT StudentID
FROM Tests
WHERE [Test Score] > 82
And [Test Name] ="Math 101"
and [Test Date] Between #2007-01-01# and #2007-03-31#
GROUP BY StudentID
HAVING Count(StudentID)>2

That would return all students with a 3 or more scores of 82 or higher on
the specified test during the specified period. IT does not say anything
about consecutive. To do that you are probably going to need a correlated
subquery of some type. Unfortunately, I can't think of a way to do this
with field names that have spaces in them. So you may have to build a query
that aliases the fields to remove the spaces and then use that as the basis
of a query that would look something like the following.

SELECT StudentID
FROM Tests as T1
WHERE [Test Score] > 82
And [Test Name] ="Math 101"
and [Test Date] Between #2007-01-01# and #2007-03-31#
AND 2 =
(SELECT Count(TestScore)
FROM
(SELECT Top 2 TestScore
FROM TESTS as T2
WHERE T2.TestDate > T1.TestDate
AND T2.TestName = T1.TestName
AND T2.StudentID = T1.StudentID
ORDER BY T1.TestDate Asc)
WHERE TestScore > 82)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..