Okay, got it - a 'range' is defined when the continuity of class dates breaks for a program. You're right, this is complicated. I don't see any way to handle this with query alone. Likely need a VBA custom function like:
Code:
Function GetEndDate(strPID As String, dteStart As Date) As Date
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ProgramID, ClassDate FROM Table2 WHERE ProgramID='" & strPID & "' ORDER BY ProgramID, ClassDate;")
Do While Not rs.EOF
If dteStart = rs!Classdate Then
Exit Do
Else
rs.MoveNext
End If
Loop
Do While Not rs.EOF
If dteStart = rs!Classdate Then
GetEndDate = rs!Classdate
dteStart = dteStart + 1
rs.MoveNext
Else
Exit Do
End If
Loop
End Function
Code assumes ProgramID is a text field. If it is a number type, remove the apostrophe delimiters from the recordset query. Also assumes multiple sessions for same program ID are not consecutive. Alternative to VBA would be using the length of a program to simply calculate the end date, assuming a program length is consistent.
Call function from query:
SELECT Table1.ProgramID, Table1.StartDate, GetEndDate([ProgramID],[StartDate]) AS EndDate FROM Table1;