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

T

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top