Find last date in a field and return data from another field in that record

[TitanMan]

I have two tables, Students and Enrollment. The Enrollment table has 5
fields: studentID (foreign key to Students table), enrollmentID
(primary key), enrollmentType, activityDate, and SchoolTransfer.

I have a Students form with a Enrollment subform. I used a main form
control to display Enrollment Type based on the last ActitvityDate in
the Enrollment Subform.

My problem is that I can't create a query to find the last activityDate
and that record's EnrollmentType. For example,
Student 1
enrollment ID (1) enrollmentType (Enrolled), activityDate (5/25/2006)
enrollment ID (2) enrollmentType (Withdrawn), activityDate (6/1/2006)

Student 2
enrollmentID (3) enrollmentType (Enrolled), activityDate (5/25/2006)
enrollmentID (4) enrollmentType (Withdrawn), actitivityDate (5/28/2006)
enrollmentID (5), enrollmentType (Enrolled), activityDate (6/1/2006)

Using the a query based on the studentID and activityDate (with Max) I
can find the most recent date. However, when I add enrollmentType to
the query it sees the most recent date with E and W as enrollment
types. I thought maybe using the form control above to populate a
field a new field in the Students table might help. Any suggestions?

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 ways to do this.

 

[TitanMan]

Thanks! I got tunnel vision. The link provided excellent help!