Problem with finding minimum date value for query

[FrankieNap]

I have two tables, Contacts, and Tests. I want to be able to compare
the actual test scores of students, located in Contacts, with the
earliest preptest scores of the same student in Tests in order to
determine the score increase.

Displaying the actual test scores in a query is simple, as there is
only one score per student. But I cannot figure out how to display the
earliest preptest scores alongside the actual test scores. I have
joined the tables based on StudentID (the primary key). When I insert
the testdate field from Tests I get all of the dates (and corresponding
scores) for that student. I want to limit the query to display only
the earlist preptest score of each student. I have tried using Dmin as
a criteria for the testdate field, but instead of finding the earliest
test date of each individual student, it returns the earliest test date
of all students put together.

Very frustrating that I have been working on this for 2 days, but I am
a teacher and programming is not my forte. Any suggestions?

Also, what is the best prep book on Microsoft Access? Basic database
construction I am fine with, but when it gets to programming and
creating calculable queries I go crosseyed.

Thanks,
-Frank

 

[Van T. Dinh]

Try some of the techniques in The Access Web:

http://www.mvps.org/access/queries/qry0020.htm