Queries with date fields

[Guest]

I posted this and got no respnonse so I thought I'd try again...
I have a query based on a table that contains the following fields -StuID,
StuName, GradeLevel, TestDate, and TestScore(each date has a corresponding
score).
A student may have anywhere from 2 to 8 records and the dates vary from
student to student. I need a series of queries that will allow me to get the
2 most recent test dates and corresponding scores for each student and find
the difference in the 2 scores.
Thanks for the assistance.

 

[John Spencer]

Sounds as if you need a coordinated subquery in your query.

SELECT StuID, StuName, GradeLevel
, First(TestDate)as OneDate
, Last(TestDate) as TwoDate
, First(TestScore) as OneTest
, Last (TestScore) as TwoTest
, First(TestScore)-Last(TestScore) as the Diff
FROM YourTable as T
WHERE TestDate in
(SELECT Top 2 TestDate
FROM YourTable as T2
WHERE T2.StuID = T.StuID
ORDER BY TestDate Desc)
GROUP BY StuID, StuName, GradeLevel

That may work for you although there is not guarantee that the dates
will be in order. That is onedate may be before or after twoDate.

If that doesn't work for you then post back and I will ponder some more