K
Kimberly3626
Hi again all!
I've managed to wrap my head around my database and find the best ways to
enter the data I need and pull it back out into reports (((GRIN))). The
problem now is a bit more complex than I've ever encountered in Access or any
other program:
I need my DB to make intelligent, judgement-based decisions for me.
Here's the background:
I have one table of Training with the following fields:
Training ID, Course Title, Position Assigned, Basic, Intermediate, Advanced,
Enrolled, and Complete
-The final five fields are yes/no. Basic training must be completed within
12 months of assignment. Intermediate within 24 months of assignment.
Advanced with 36 months of assignment.
My second table is Personnel Data, which has the following fields being
utilized in my query: Last Name, First Name, (relationship from Position to
Position is not a field, but the relationship links the person to the
training), Duty Position, and Date Assigned to Position.
I have the query pulling all of these fields from the two tables correctly.
The criteria I have set under 'Completed' is "=False." It is correctly only
pulling incomplete training for me. (This, by the way, is a huge
accomplishment!!!)
Now...what I need is a the following report:
If the Difference between Date Assigned and Today is > 12 Months, training
assigned in the Basic category is Overdue.
If the Difference between Date Assigned and Today is > 24 Months, training
assigned in the Intermediate category is Overdue.
If the Difference between Date Assigned and Today is > 36 Months, training
assigned in the Advanced category is Overdue.
In addition, I need it to sort within those three 'overdue categories' so
that the MOST overdue comes up at the top of the list.
This will allow us to prioritize the training needs of the unit based on
actual due dates! *gasp*...The shock!!!
So...Is it possible?
(To get my summaries of training by person, I was able to develop three
queries, each pulling one training category, then put all three queries into
a report as individual sub-reports. This shows your personal information at
the top, then your Basic training requirements and status, then Intermediate,
then Advanced. I can do the same for this if need be to make it work, since
I'm trying to define three different categories.)
One thing: It is possible that Person A has been assigned for 7 years and
still hasn't done a basic course, or that someone who has only been assigned
4 hasn't completed an advanced course. I need it to sort, regardless of
training category, into what is the MOST overdue. It is possible that an
advanced course could be more overdue than a basic and so forth....
I've managed to wrap my head around my database and find the best ways to
enter the data I need and pull it back out into reports (((GRIN))). The
problem now is a bit more complex than I've ever encountered in Access or any
other program:
I need my DB to make intelligent, judgement-based decisions for me.
Here's the background:
I have one table of Training with the following fields:
Training ID, Course Title, Position Assigned, Basic, Intermediate, Advanced,
Enrolled, and Complete
-The final five fields are yes/no. Basic training must be completed within
12 months of assignment. Intermediate within 24 months of assignment.
Advanced with 36 months of assignment.
My second table is Personnel Data, which has the following fields being
utilized in my query: Last Name, First Name, (relationship from Position to
Position is not a field, but the relationship links the person to the
training), Duty Position, and Date Assigned to Position.
I have the query pulling all of these fields from the two tables correctly.
The criteria I have set under 'Completed' is "=False." It is correctly only
pulling incomplete training for me. (This, by the way, is a huge
accomplishment!!!)
Now...what I need is a the following report:
If the Difference between Date Assigned and Today is > 12 Months, training
assigned in the Basic category is Overdue.
If the Difference between Date Assigned and Today is > 24 Months, training
assigned in the Intermediate category is Overdue.
If the Difference between Date Assigned and Today is > 36 Months, training
assigned in the Advanced category is Overdue.
In addition, I need it to sort within those three 'overdue categories' so
that the MOST overdue comes up at the top of the list.
This will allow us to prioritize the training needs of the unit based on
actual due dates! *gasp*...The shock!!!
So...Is it possible?
(To get my summaries of training by person, I was able to develop three
queries, each pulling one training category, then put all three queries into
a report as individual sub-reports. This shows your personal information at
the top, then your Basic training requirements and status, then Intermediate,
then Advanced. I can do the same for this if need be to make it work, since
I'm trying to define three different categories.)
One thing: It is possible that Person A has been assigned for 7 years and
still hasn't done a basic course, or that someone who has only been assigned
4 hasn't completed an advanced course. I need it to sort, regardless of
training category, into what is the MOST overdue. It is possible that an
advanced course could be more overdue than a basic and so forth....