That's what the second query in the link does.
qryApptT_NearestToday
SELECT tblTAppt.TRef, Max(tblTAppt.ApptEnd_Dt) AS Nearest_Dt
FROM tblTAppt
GROUP BY tblTAppt.TRef;
qryTutor_Rank_NearestToday
SELECT qryApptT_NearestToday.TRef, qryApptT_NearestToday.Nearest_Dt, tblTutor.LastName, tblTutor.FirstName, tblTRank.HDesc, tblTAppt.ApptEnd_Dt
FROM tblTRank INNER JOIN (tblTAppt INNER JOIN (qryApptT_NearestToday INNER JOIN tblTutor ON qryApptT_NearestToday.TRef = tblTutor.TRef) ON tblTAppt.TRef = qryApptT_NearestToday.TRef) ON tblTRank.ID = tblTAppt.RankCode;
Once I added "tblTAppt.ApptEnd_Dt = qryApptT_NearestToday.Nearest_Dt" into the above query and the query is shown below. It runs into error of "Syntax error in JOIN operation". I am stuck the query construct.
qryTutor_Rank_NearestToday
SELECT qryApptT_NearestToday.TRef, qryApptT_NearestToday.Nearest_Dt, tblTutor.LastName, tblTutor.FirstName, tblTRank.HDesc, tblTAppt.ApptEnd_Dt
FROM tblTRank INNER JOIN (tblTAppt INNER JOIN (qryApptT_NearestToday INNER JOIN tblTutor ON qryApptT_NearestToday.TRef = tblTutor.TRef) ON tblTAppt.TRef = qryApptT_NearestToday.TRef) ON tblTRank.ID = tblTAppt.RankCode AND tblTAppt.ApptEnd_Dt = qryApptT_NearestToday.Nearest_Dt;
tblTutor (TRef is the primary key)
tblTAppt (TRef is a foreign key)
qryAppt_NearestToday (TRef is a foreign key)
Please see the attached file for the tables relationship.
Would you suggest for the query construct.
Thank you.