Re: Struggling to think of how to create a query/report that would result in this
You are going to need at least a junction table. You can look this up in the forum using the SEARCH feature in the ribbon near the top of the page. SEARCH is 3rd from the right.
You need a table of unique drills which includes a drill ID that will be that table's prime key (PK). You need a table of programs which includes a program ID that will be that table's PK. You will need a junction table that includes the following fields:
Drill ID, long, foreign key pointing to drill table's appropriate record.
Program ID, long, foreign key pointing to drill table's appropriate record.
DueDate, date, date when this drill must be performed
DateDone, date, date when this drill was performed by this program, initialized to specific date 31-Dec-9999 (and yes, Access will go that high.)
Then, drills are due if the DateDone is later than (greater than) the DueDate. You could do a JOIN query on the junction and program tables in order to generate the report.
Then the trick will be to populate the junction table.
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.