I'm going to suggest a "divide and conquer" type of thing.
First, your "schedule" table MIGHT look like this (and it resembles OUR schedule table though from a different environment):
tblSchedItem
SIID - Prime Key (PK), autonumber is probably adequate
SchInterval = text, one character = m for monthly, d for daily, h for hourly, y for yearly, w for weekly, etc.
SchMonth = for yearly items, month in which it runs else 0
SchDay = for monthly & yearly items, day of month on which it runs else 0 and for weekly items, day of WEEK on which it runs or -1 (have to use -1 for this because 0 = Sunday in Access.)
SchHour = for yearly, monthly, weekly, and hourly items - hour of day at which it runs, 00 is midnight, set to -1 if not hourly.
SchMinute = for all of the above, minute of the hour at which it runs, 00 means top of the hour, and this can be -1 in only one case. The value of this field has to be an integer multiple of the scheduler base frequency. For us, every 15 minutes, so the values are 00, 15, 30, and 45. Plus the -1 value meaning "every cycle" But then, we have some itty-bitty jobs that actually DO run that often, for security scan purposes and some other things we do.
SchItem - a reference to the actual item to be executed.
If you run a weekly report 3 times a week on Mon, Wed, Fri then you have three entries for that report, once for each day of the week. In this scheme, no harm exists in repeating an entry if the corresponding report runs that often as well.
OK, mind-set issue: What is "due" to your business? What is "overdue" to your business?
Now, a single query does not get you what you want no matter what you want - unless there is a UNION query involved. This might also be a "VBA" situation in which you have a little subroutine that scans your schedule recordset and sets a flag for any record that is due at a given scan cycle. The key is that a schedule is not tested by a time, but by a pair of times - previous run and current run.
OK, how would you use it? Well, for starters, before ANY scan of the schedule, reset all Y/N flags you were using to track "Due" or "Overdue" or "Projected as due" or whatever.
First, for scheduling, you need to know the last time you ran the scheduler (which should be a DMAX() of the "ran the scheduler" history table). Then take that time and the current time. For each record, set a Y/N type "due" flag based on looking at the current time. Set an "overdue" Y/N flag based on looking at the last time you ran the scheduler and the current time. Alternative: Each entry in the schedule table should ALSO have a date/time of most recent run, which allows you to determine via VBA-type logic whether the item ran on schedule or not. The trick is that for the given schedule frequency, at least for us, an item isn't overdue unless it should have been run twice in the given interval.
For workload projections, have another Y/N flag called "projected due" (or something like that) where you give it two arbitrary times and have it set or clear the projection flag if the item would be due on that date. Then count the projected due flags.
The idea we use in our BASIC language algorithm is we start a test of the schedule by iterating the run cycles between the last run date and the current run date. For a given record, because the disk I/O is more expensive, we loop through the possible dates. When we are done, we have a count of the number of times that the individual record should be scheduled in the interval.
Answers could be 0, 1, or many. Zero is easy. Not due. Skip it.
One is fairly easy - though we have lately added a wrinkle: If the count is one but the cycle that triggered the count wasn't the current time, set the OVERDUE flag as well as the DUE flag.
A return value of MANY is very easy. No matter what it was, it is overdue at least once. Maybe more than once.
The key for us is that little jewel that iterates through the date range incrimented by the scheduler's run-time interval, in essence a DO WHILE (step-time < current-time) loop. Then for each step we flag whether it was due or not. It isn't that hard to write. The only nasty part is that you need different SELECT/CASE statements for the different intervals of yearly, monthly, weekly, daily, or other intervals.
I'd write it for you but the trick is, I don't know your business rules and I'm kind of busy at the moment preparing for a business trip. But this should give you an overview of what is going on.
By the way, we added another little wrinkle to this. We break out the various parts of the date inside the iteration so we know the current month, weekday, day of month, hour of day, minute of hour etc. But we ALSO know from a little test whether a given day is the last day of the month. (If today is the last day of the month, tomorrow is the first day of another month, so it has a DatePart of 1 for the day number). So for our monthly items, we define an impossible day number to represent "last day of month." For non-monthly items, we use 0, so for last-day items we use -1.
In summary, your schedule becomes a run of the interval tester to see what is due in the current interval. Your projected load on day X becomes a run of the interval tested to see what is due in the future interval. Your "overdue" is a wrinkle within the tester that makes a special mark when an entry should have run - but did not run - during a given interval, or when the count of expected runs is > 1.
