Don't need real data, just realistic data. You could build dummy db to provide structure and some simple data for testing. Or build tables in forum post with the Advanced editor. Help us help you.
How do you determine checkin dates? Are these calculated from date enrolled in program? Enrollment date of 2/11/2023 would have 3-month date of 5/11/2023?
Checkin is missed if field is empty and calculated date is passed?
Consider:
Code:
SELECT ID, EnrollDate, DateAdd("m",3,[EnrollDate])<Date() And [Mon3] Is Null AS M3, DateAdd("m",6,[EnrollDate])<Date() And [Mon6] Is Null AS M6
FROM Table1
WHERE (((DateAdd("m",3,[EnrollDate])<Date() And [Mon3] Is Null)=True)) OR (((DateAdd("m",6,[EnrollDate])<Date() And [Mon6] Is Null)=True));
or
Code:
SELECT * FROM (
SELECT ID, EnrollDate, "M3" AS Per, Mon3 AS DateReview, DateAdd("m",3,[EnrollDate])<Date() And [Mon3] Is Null AS IsMissed FROM Table1
UNION SELECT ID, EnrollDate, "M6", Mon6, DateAdd("m",6,[EnrollDate])<Date() And [Mon6] Is Null FROM Table1) AS DataUNION
WHERE IsMissed;