Query Last Record as of Certain Date

[rachello89]

I have two tables: tblEnrollments and tblEnrollmentProduct. They are joined by the field EnrollmentID with a one to many relationship. An enrollment can have many enrollment products. I would like to design a query that searches for the last record as of a certain date. I know how to find the last record, but when I add a date criteria it doesn't match what I want to see. Anyone know how to solve this? I would appreciate your help! Below are the details of what I need.

tblEnrollment fields: EnrollmentID, EnrollmentDate, and EnrollmentEndDate.
tblEnrollmentProduct: EnProdID, EnrollmentID, EnrollmentProduct, StartDate, EndDate.



For every EnrollmentID, I need to see the latest EnProdID where the StartDate is less than or equal to a date that I provide. The EnProdID would change if typed in a later or earlier date. For instance, Enrollment 5050 has EnProdIDs and StartDates of 8049, 1/26/2012; 8094, 2/1/2012; and 8151, 4/1/2012. If I type in 2/29/2012 or 3/12/2012, I want to see EnProdID 8094, and if I type 4/2/2012, I want to see 8151 next to enrollment 5050.

[pbaldy]

It sounds like you want this:

http://www.baldyweb.com/LastValue.htm

You should be able to add a WHERE clause to the first to have it restrict to records before a given date.

[rachello89]

Thanks! Just what I needed.

[pbaldy]

Happy to help!