Date Disaster

[Guest]

Oh, please help, someone! I have a database which records karate students and their testing dates and belt ranks... among other things. I have a table with an Auto ID, Student Name, Promotion Rank (color of belt), and Promotion Date. Each student will have many entries as they progress through the ranks. However, I need to query on the very LAST promotion date. I am at a stone wall. Can some one offer a lifeline? If you can, I will be in your debt forever. Thank you in advance for your time.

 

[Ken Snell]

The last promotion date will be the maximum date value for that student.
Perhaps using the DMax function in a criterion expression is what you need?

Not sure if you just want a regular "display" of the record associated with
each student, but assuming so, here is one way to do what you seek:

SELECT T.[Student Name], T.[Promotion Rank], T.[Promotion Date]
FROM Tablename AS T
WHERE T.[Promotion Date] =
DMax("Promotion Date", "Tablename", "[Student Name]='" &
T.[Student Name] & "'");

--
Ken Snell
<MS ACCESS MVP>

Oh, please help, someone! I have a database which records karate students

and their testing dates and belt ranks... among other things. I have a table
with an Auto ID, Student Name, Promotion Rank (color of belt), and Promotion
Date. Each student will have many entries as they progress through the
ranks. However, I need to query on the very LAST promotion date. I am at a
stone wall. Can some one offer a lifeline? If you can, I will be in your
debt forever. Thank you in advance for your time.