Rolling date check in Query

[cp3]

I have a database with many columns pertaining to dates (I.E. a 3 mnth check in column, 6 mnth check in column, 9 mnth column etc...). I want to be able to pull missing data with a query specially aimed at certain time frames. For example, I'd like to pull all of the records who were missing their most recent check in, however the records are not all on the same check in. Records were entered in batches on a rolling scale so some are on their 3mnth check in column, and others are on their 6 mnth, 9mnth etc....

I am totally stumped on this. How would I create a query that pulls every record that missed their most recent check in without excluding other records since they might have been included in the study at a different time. I am just starting out in Access and everyone's input is so helpful! I definitely wouldn't be as far as I am without this forum!

[June7]

Could you provide db for analysis? Follow instructions at bottom of my post.

[cp3]

Unfortunately I cannot do that. This database has confidential data and cannot be removed from the network it is stored on. But if you have any advice for this question or perhaps explain your approach here I would much appreciate it

[Welshgasman]

Your issue is because you do not have a normalized database.

Generally to get around such a badly designed db, you need to create a union query with as many query statements as you have columns, for the data you are trying to interrogate.

[June7]

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:

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

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;

[Welshgasman]

Unfortunately I cannot do that. This database has confidential data and cannot be removed from the network it is stored on. But if you have any advice for this question or perhaps explain your approach here I would much appreciate it

Use this. https://www.accessforums.net/showthr...t=randomize#19