Interesting Sort / Criteria / Query / Report question:

[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....

 

[Allen Browne]

The 5 yes/no fields are the problem. Consider replacing them with a StatusID
field.

This field will refer to the primary key of another little lookup table,
with fields like this:
- StatusID primary key
- Months2Complete Number
So, the records will look like this:
StatusID Months2Complete
Basic 12
Intermediate 24
Advanced 36
Complete

Now, you can create a query using both tables, and type an expression like
this into the Field row:
DueDate: DateAdd("m", [Months2Complete], [Date Assigned])

 

[Kimberly3626]

So should I eliminate the Basic, Intermediate, and Advanced fields
completely? Then create a new field called 'MonthsToComplete'?
I'm concerned about ease of use for the database forms and my replacement...
Right now, the checkboxes make his life very easy. However, I can see the
benefit to the combined column with the months requirement...Just in case our
higher HQ decides to make Basic an 18 month requirement, etc.
I like that, right now...when he opens the form and selects the class from
the dropdown, the checkboxes tell him what positions must complete which
training and in what phase.

Could I possibly ADD a column to the table? Maybe an autofill that's
invisible to my user, but does something like this: If Basic = True, then
this field = 12? Will that work at all? And will it autofill? Or even have
that new column, instead of saying 12, add 365 days to the date assigned to
generate a due date? (which is present on the aforementioned form)

Sorry to be so confusing...This is just making me nutty!

The 5 yes/no fields are the problem. Consider replacing them with a StatusID
field.

This field will refer to the primary key of another little lookup table,
with fields like this:
- StatusID primary key
- Months2Complete Number
So, the records will look like this:
StatusID Months2Complete
Basic 12
Intermediate 24
Advanced 36
Complete

Now, you can create a query using both tables, and type an expression like
this into the Field row:
DueDate: DateAdd("m", [Months2Complete], [Date Assigned])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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....

.

 

[Kimberly3626]

Ok, it's early. I think I understand now. I'll be using another table to
pull those months, which I think might work out perfectly.
Off to test, and I'll come back if I can't make it work! (50/50 chance with
the way things are going so far today)

The 5 yes/no fields are the problem. Consider replacing them with a StatusID
field.

This field will refer to the primary key of another little lookup table,
with fields like this:
- StatusID primary key
- Months2Complete Number
So, the records will look like this:
StatusID Months2Complete
Basic 12
Intermediate 24
Advanced 36
Complete

Now, you can create a query using both tables, and type an expression like
this into the Field row:
DueDate: DateAdd("m", [Months2Complete], [Date Assigned])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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....

.