Struggling to think of how to create a query/report that would result in this (1 Viewer)

kvnd

Registered User.
Local time
Today, 05:33
Joined
Oct 9, 2018
Messages
16
I have an example of my data set in the two tables attached below. The data set is a table of drills where the ID field lists unique drills performed. The ProgramTable lists all the locations and their drill requirements.

I'm trying to create a report or query that would, according to today's date (Say, May 16th), list each program and the drills they are supposed to and eligible to complete. So this would result in something like the report featured in the image for my example data set.

According to my data set, Program 123 has not completed a 2nd shift drill this quarter yet, 288 has already completed a drill for the first half of the year, 082 has not, and 324 has not done a drill this month.

The challenge is creating the field/calculation "Current Requirements" in the image which tells this to the user. I'm not sure where to begin to achieve this. The farthest I've gotten is a report which lists the programs and the count of the drills they've completed this year.

Which approach should I take? I'm pretty inexperienced in Access. I can program something like this in Tableau and python, but I'm not sure how to do it here.

Thanks.
 

Attachments

  • ReportExample.jpg
    ReportExample.jpg
    43 KB · Views: 91
  • DrillTable.xlsx
    9.1 KB · Views: 102
  • ProgramTable.xlsx
    9 KB · Views: 97

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:33
Joined
Feb 28, 2001
Messages
27,001
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.
 

Users who are viewing this thread

Top Bottom