BrokenBiker
ManicMechanic
- Local time
- Today, 14:55
- Joined
- Mar 22, 2006
- Messages
- 128
As a general rule, I use many baby-steps to get to a desired result. Unfortunately, this often makes things over complicated. So, if y'all don't mind, take a look at my example and see if there's a simpler way of getting the end product.
With that being said...
This db is used to track training. I want this db to record calculated figures in a table to use as reference points to show progression in training.
For a trainee to be "signed off" on a task, that task requires both the trainer's and trainee's initials entered in separate fields.
The queries are numbered in functional order.
The table "tbl_ImportTemp" is the result of a mass import from Excel files from another system. (No, I don't have access to the other system's data/tables.) I don't get any import errors, but...the "tbl_ImportTemp" will have records which contain empty fields.
Part of the query "qry_5LVL_1_ParseExtraction"'s function is to extract only the relevant records and exclude the empty-field records.
"qry_5LVL_2_CreateTable" then creates a table "tbl_5LVL_ParsedInfo". I draw from this table to count in two separate queries the total number of tasks per individual and the number of tasks signed off. ("qry_5LVL_3_TaskCount" & "qry_5LVL_4_SOCount")
I then draw from those two queries to get a percentage of tasks in "qry_5LVL_5_PercQual." Then I run an update query, "qry_5LVL_6_Updatetbl_PercQual", to update the table "tbl_5LVL_PercQual."
The purpose of the Update query is to record the percentages. Everytime Excel files are imported (once per week), this process will be run. The "tbl_5LVL_PercQual" table will then have a running history of previous percentages/qualifications.
Last, but not least, I run the "qry_5LVL_7_PercQual_History" to organize the info.
With that being said...
This db is used to track training. I want this db to record calculated figures in a table to use as reference points to show progression in training.
For a trainee to be "signed off" on a task, that task requires both the trainer's and trainee's initials entered in separate fields.
The queries are numbered in functional order.
The table "tbl_ImportTemp" is the result of a mass import from Excel files from another system. (No, I don't have access to the other system's data/tables.) I don't get any import errors, but...the "tbl_ImportTemp" will have records which contain empty fields.
Part of the query "qry_5LVL_1_ParseExtraction"'s function is to extract only the relevant records and exclude the empty-field records.
"qry_5LVL_2_CreateTable" then creates a table "tbl_5LVL_ParsedInfo". I draw from this table to count in two separate queries the total number of tasks per individual and the number of tasks signed off. ("qry_5LVL_3_TaskCount" & "qry_5LVL_4_SOCount")
I then draw from those two queries to get a percentage of tasks in "qry_5LVL_5_PercQual." Then I run an update query, "qry_5LVL_6_Updatetbl_PercQual", to update the table "tbl_5LVL_PercQual."
The purpose of the Update query is to record the percentages. Everytime Excel files are imported (once per week), this process will be run. The "tbl_5LVL_PercQual" table will then have a running history of previous percentages/qualifications.
Last, but not least, I run the "qry_5LVL_7_PercQual_History" to organize the info.
