LittleJohn LittleJohn - 13 days ago 4
SQL Question

How can I compare dates in related tables and use this to select the relevant record in a query? SQL / MS Access

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):

Class Details


  • ClassID (Primary key)

  • CourseID (Foreign key)

  • Commencement Date



Courses


  • CourseID (Primary key)



Course Versions


  • CourseVersionID (Primary key)

  • CourseID (Foreign key)

  • Course Version Start Date



I want to show one record per unique ClassID in a query with the following columns:


  • ClassID

  • The Course Version Start Date immediately below the Commencement Date (see examples below)




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.



  • CourseVersionID



Solutions that can be used without much SQL knowledge (ie within the Access design view) are prefered but not necessary.

Answer

In SQL:

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:

Access Query Design