Displaying records outside of a date range.

[Guest]

I have a query that uses the expression below. As it is, it shows active
patients who have an appointment within the date range the user enters, but
drops the patients that don't. I need it to always show ALL the active
patients, but indicate that they have no appointments in the specified date
range. Any suggestions would be appreciated.


HAVING (((PatientDay.Date) Between [Enter begin date:] And [Enter end
date:]) OR ((Patient.DischargeDt) Is Null)) AND (([All Active Home
Care].Team)<>"HOS")

 

[Michel Walsh]

Hi,




SELECT a.*, EXISTS( SELECT *
FROM appointments AS b
WHERE b.patientID=a.patientID
AND b.appDate BETWEEN date1 AND date2)
FROM clients AS a




or, with a join:

SELECT a.patientID, COUNT(b.patientID)<>0
FROM patients AS a LEFT JOIN
(SELECT patientID
FROM appointments
WHERE dateApp BETWEEN date1 AND date2) AS b
ON a.patientID = b.patientID
GROUP BY a.patientID


With MS SQL Server, you can do the latter more succintly:


SELECT a.patientID, COUNT(b.patientID)<>0
FROM patients AS a LEFT JOIN appointments AS b
ON a.patientID = b.patientID
AND dateApp BETWEEN date1 AND date2
GROUP BY a.patientID




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you for the quick response. I think I've gotten past any knowledge
base I thought I might have had, so I'm going to give your logic a shot and
see how it goes.

Thanks!
Bullmann

Hi,




SELECT a.*, EXISTS( SELECT *
FROM appointments AS b
WHERE b.patientID=a.patientID
AND b.appDate BETWEEN date1 AND date2)
FROM clients AS a




or, with a join:

SELECT a.patientID, COUNT(b.patientID)<>0
FROM patients AS a LEFT JOIN
(SELECT patientID
FROM appointments
WHERE dateApp BETWEEN date1 AND date2) AS b
ON a.patientID = b.patientID
GROUP BY a.patientID


With MS SQL Server, you can do the latter more succintly:


SELECT a.patientID, COUNT(b.patientID)<>0
FROM patients AS a LEFT JOIN appointments AS b
ON a.patientID = b.patientID
AND dateApp BETWEEN date1 AND date2
GROUP BY a.patientID




Hoping it may help,
Vanderghast, Access MVP

I have a query that uses the expression below. As it is, it shows active
patients who have an appointment within the date range the user enters,
but
drops the patients that don't. I need it to always show ALL the active
patients, but indicate that they have no appointments in the specified
date
range. Any suggestions would be appreciated.


HAVING (((PatientDay.Date) Between [Enter begin date:] And [Enter end
date:]) OR ((Patient.DischargeDt) Is Null)) AND (([All Active Home
Care].Team)<>"HOS")