Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2019, 08:21 AM   #1
kvnd
Newly Registered User
 
Join Date: Oct 2018
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
kvnd is on a distinguished road
Smile Struggling to think of how to create a query/report that would result in this

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.
Attached Images
File Type: jpg ReportExample.jpg (43.0 KB, 22 views)
Attached Files
File Type: xlsx DrillTable.xlsx (9.1 KB, 14 views)
File Type: xlsx ProgramTable.xlsx (9.0 KB, 7 views)

kvnd is offline   Reply With Quote
Old 05-16-2019, 12:01 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,929
Thanks: 79
Thanked 1,562 Times in 1,450 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
kvnd (05-20-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help to create report from a search result atut Reports 1 05-29-2012 04:55 AM
How to create a Report from the result of a Filter? Ashraf Reports 2 05-17-2010 02:00 PM
Create a report from search result on a form..can I? CarysW Forms 1 06-22-2009 11:35 PM
Report to show a query result and a table result coffeeman Reports 1 12-30-2008 11:43 PM
Report to show a query result and a table result coffeeman Queries 1 12-30-2008 03:04 PM




All times are GMT -8. The time now is 10:41 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World