Finding a list of personal records

[860sharp]

I am a Track Coach and I have a Database that I use to keep track of
my top times.
My "Times Table" has fields of Name, Event, SprintTime, Grade, DateOf
Mark, Gender.

I have a query that will produce the best mark for each kid BEFORE a
specific date.
I have another query that will show the best mark ON a certain date

Here is what I am trying to Accomplish.
I would like to have a query combing the previous two queries that
produces a list of kids who ran faster than their previous best.
Basically, a list of kids that ran personal records that week

Example: Joe 10.65 has best of before 3/15/07
If Joe runs 10.64 on 1/31/08, then his name will show
up.
If he were to run 10.66, then his name would not.

I'm sure its not very hard. But I'm a little stuck,
Thanks

 

[Jeanette Cunningham]

Hi,
try something like this

SELECT tblScores.LName, tblScores.Score, tblScores.ScoreDate
FROM tblScores
WHERE (((tblScores.Score)=(SELECT Max(tblScores.Score) AS MaxOfScore
FROM tblScores)) AND ((tblScores.ScoreDate)=#1/9/2008#))
ORDER BY tblScores.LName;

Jeanette Cunningham