A much more complicated subquery approach is
done by a lot of trial and errors.
This is painstaking and not recommended for normal users.
The code of the query (qryChaplain Daily List Query) is rewritten as follows:-
Code:
SELECT tblContact.Date, tblContact.RoomNumber,
tblContact.PatientName, tblContact.PatientMRN,
tblContact.Discharged, tblContact.City, tblContact.Code,
tblContact.ParishName, tblContact.Memo,
tblContact.PatientDeclines, tblContact.PatientFirstName,
tblContact.PatientMiddleInitial, tblContact.CheckBack,
tblContact.Phone, tblContact.Communion, tblContact.[City
Name], tblContact.PetTherapy, tblContact.PetType, [tblTime
Data].ReturnDate, [tblTime Data].ReturnPatient
FROM tblContact LEFT JOIN [tblTime Data] ON
((tblContact.PatientMRN=[tblTime Data].[Patient MRN])
)
WHERE (
((tblContact.Discharged)=False)
and
(
([tblTime Data].ReturnDate is null) or
(
[tblTime Data].ReturnDate in
(
select top 1 [tblTime Data].ReturnDate
FROM [tblTime Data]
WHERE [tblTime Data].[Patient MRN] = [tblContact].[PatientMRN]
ORDER BY [tblTime Data].ReturnDate DESC
)
)
)
)
ORDER BY tblContact.RoomNumber;