If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Interesting Sort / Criteria / Query / Report question:
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.... |
#2
|
|||
|
|||
Interesting Sort / Criteria / Query / Report question:
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kimberly3626" wrote in message ... 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.... |
#3
|
|||
|
|||
Interesting Sort / Criteria / Query / Report question:
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! "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kimberly3626" wrote in message ... 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.... . |
#4
|
|||
|
|||
Interesting Sort / Criteria / Query / Report question:
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) "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kimberly3626" wrote in message ... 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.... . |
Thread Tools | |
Display Modes | |
|
|