The details of a course change over time. I need to be able to access the version of these details that was current when the course was run.
My tables are setup as follows (only relevant fields are shown):
Course Version Start Date (1) = 01/01/2010
Course Version Start Date (2) = 01/11/2011
Commencement Date is between (1) and (2) ~ The returned value is 01/01/2010
Commencement Date is greater than (2) ~ The returned value is 01/11/2011
Commencement Date is less than (1) ~ The returned value is nothing, this record doesn't show up.
SELECT Class.ID, Max(CourseVersions.CourseVersionStartDate) AS CourseVersionStartDate FROM (Course INNER JOIN Class ON Course.CourseId = Class.CourseId) INNER JOIN CourseVersions ON Course.CourseId = CourseVersions.CourseId WHERE (((Class.CommencementDate) > CourseVersions.CourseVersionStartDate)) GROUP BY Class.ID;
Or, since you prefer the access query designer: