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
|
|||
|
|||
Training database
Is there any way to put this into plain english I am much better at visual
instruction, I to am trying to build a training data base that can generate reports of who has been trained and be able to assign certain training modules to certain job functions. Here is what I want. 1. A list of employees, what location they work at, what they have been trained on, what they still need to be trained on, who trained them, when they were trained, and when they are due to be retrained. It would be nice to be able to have several employees enrolled in every class. I have tables set up as follows tbl 1. employee list (all employees) Emp ID (primary key) all pertinent employee info address,name,city,state etc. tbl 2. locations (all locations over 60) location code (primary key) all pertinent info address, manager, etc. tbl 3. training modules (all training offered) module code (primary key) module name instructor id (im thinking i dont need this as all instructors can teach all the training) tbl 4. training sessions session id (primary code) module name start date end date instructor id test score employee id location code (not sure if I need this here) module code training man hours tbl 5. Instructors instructor id (primary key) instructor name phone # tbl 6. job functions id (primary key) auto number job name tbl 7. completed training (called students & classes in CRM templat) completed class id (primary key) employee id module code grade tbl 8. results (not sure if needed but it was in the classroom mgmt template) looks redundant to me results id employee id session id grade I am unsure of how to set up the relationships to the tables correctly and reports and querys. I would like to use the structure in the classroom management and Events template. any help would be appreciated. And no I do not want to purchase one. I want to learn how to build it. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#2
|
|||
|
|||
Training database
HI,
A couple of questions. Can an employee be assigned to multiple locations at the same time. Could he work parttime at one location and part time at another location? If an employee gets tranferred from one location to another location, do you want to keep track of the old location information or will it be lost? If an employee get transferred or get changes jobs within the company, does his class requirement change? To respond to one of your quesitons: tbl 3. training modules (all training offered) module code (primary key) module name instructor id (remove from this table because they are associated with the session, not the module). I would split the Training session table into two tables and combine then completed training and results tables into a single table as follows: Training Table session id (primary code) module code start date end date START TIME I don’t know if you care about this. END TIME instructor id training man hours location code (not sure if I need this here) – I’m assuming this is the location code of the class and not the employee’s location code. If this is the employee’s location code then you need to remove it. If this is the class’s location code, then I would keep it. (remove the module name as the module code will get you the name) MOVE test score TO EmployeeTraining Table MOVE employee id TO EmployeeTraining Table EmployeeTraining Table (Combination of your training table, Completed Training, and Results tables). employeeTrainingId – automatically assigned number. session id employee id StatusFlag – Value of 1 if registered, 2 if missed class, 3 if attended class, Grade Drop the Completed and Results tables. You are going to need to add a table that has the required class per job function and how often they need to be re-trained on that class such as: Job Training Job Function Id Module Code Frequency Final table design: tbl 1. employee list (all employees) Emp ID (primary key) all pertinent employee info address,name,city,state etc. tbl 2. locations (all locations over 60) location code (primary key) all pertinent info address, manager, etc. tbl 3. training modules (all training offered) module code (primary key) module name tbl 4. Training Session Table session id (primary code) module code start date end date START TIME I don’t know if you care about this. END TIME instructor id training man hours location code (if location of class) tbl 5. Instructors instructor id (primary key) instructor name phone # tbl 6. job functions id (primary key) auto number job name tbl 7. EmployeeTraining Table employeeTrainingId – automatically assigned number. session id employee id StatusFlag – Value of 1 if registered, 2 if missed class, 3 if attended class, Grade Relationship Tbl 1. Employee List LocationNo - Many to 1 link to Location table (many employees in one location) JobFuction – Many to 1 link to Job Function (many employees have same job function) Tbl 2. Location State – Links to the state table. Tbl 3 – Training Module Tbl 4 – Training Session Module code – Many to 1 link to Training Module (multiple sessions to 1 module) Instructor Id – Many to 1 link to Instructor Table (multiple classes to 1 instructor) Location Code – Many to 1 link to Location (multiple classes at 1 location) Tbl 5 – Instructor Tbl 6 – Job Function Tbl 7 – Employee Training Session ID – Many to 1 relation to Training Session (many students in one session) Module Code in Session table – Many to 1 relation to Training Module Table. EmployeeId - Many to 1 relation to Employee (many classes for 1 employee) Tbl 8 - Job Traning Job Function Id – Many to 1 relationship to Job Function Tbl (many classes to 1 function) Module Code – Many to 1 relationship to Training Module (many job functions to 1 class) Frequency – how often does this need to be re-trained. The above will answer the following questions: A list of employees, - what location they work at, - what they have been trained on - and who trained them - when they were trained While the above structure will allow you to determine the answers to the following questions, you will need to figure out how to: - determine what an employee still needs to be trained on, - determine when they are due to be retrained. I don’t know how to do this last part, but the above database structure should enable you to figure it out. You would link the employee table to the job function table, then link the job function table to the Job Traning table. This query would provide a list of all of the classes the employee should take. You would then link employee table to the Employee Training Table and this will give you a list of all of the classes the employee has taken. You will then need to figure out how to use these two queries to provide a report of classes still needing to be taken. I don’t know how to do that. Hopefully someone else will be able to provide assistance. Good luck Dennis |
#3
|
|||
|
|||
Training database
You can buy a ready-made Access 2007 training database at
http://www.trainingdatabase.com and tweak it to meet your needs. They are very affordable. "Dennis" wrote: HI, A couple of questions. Can an employee be assigned to multiple locations at the same time. Could he work parttime at one location and part time at another location? If an employee gets tranferred from one location to another location, do you want to keep track of the old location information or will it be lost? If an employee get transferred or get changes jobs within the company, does his class requirement change? To respond to one of your quesitons: tbl 3. training modules (all training offered) module code (primary key) module name instructor id (remove from this table because they are associated with the session, not the module). I would split the Training session table into two tables and combine then completed training and results tables into a single table as follows: Training Table session id (primary code) module code start date end date START TIME I don’t know if you care about this. END TIME instructor id training man hours location code (not sure if I need this here) – I’m assuming this is the location code of the class and not the employee’s location code. If this is the employee’s location code then you need to remove it. If this is the class’s location code, then I would keep it. (remove the module name as the module code will get you the name) MOVE test score TO EmployeeTraining Table MOVE employee id TO EmployeeTraining Table EmployeeTraining Table (Combination of your training table, Completed Training, and Results tables). employeeTrainingId – automatically assigned number. session id employee id StatusFlag – Value of 1 if registered, 2 if missed class, 3 if attended class, Grade Drop the Completed and Results tables. You are going to need to add a table that has the required class per job function and how often they need to be re-trained on that class such as: Job Training Job Function Id Module Code Frequency Final table design: tbl 1. employee list (all employees) Emp ID (primary key) all pertinent employee info address,name,city,state etc. tbl 2. locations (all locations over 60) location code (primary key) all pertinent info address, manager, etc. tbl 3. training modules (all training offered) module code (primary key) module name tbl 4. Training Session Table session id (primary code) module code start date end date START TIME I don’t know if you care about this. END TIME instructor id training man hours location code (if location of class) tbl 5. Instructors instructor id (primary key) instructor name phone # tbl 6. job functions id (primary key) auto number job name tbl 7. EmployeeTraining Table employeeTrainingId – automatically assigned number. session id employee id StatusFlag – Value of 1 if registered, 2 if missed class, 3 if attended class, Grade Relationship Tbl 1. Employee List LocationNo - Many to 1 link to Location table (many employees in one location) JobFuction – Many to 1 link to Job Function (many employees have same job function) Tbl 2. Location State – Links to the state table. Tbl 3 – Training Module Tbl 4 – Training Session Module code – Many to 1 link to Training Module (multiple sessions to 1 module) Instructor Id – Many to 1 link to Instructor Table (multiple classes to 1 instructor) Location Code – Many to 1 link to Location (multiple classes at 1 location) Tbl 5 – Instructor Tbl 6 – Job Function Tbl 7 – Employee Training Session ID – Many to 1 relation to Training Session (many students in one session) Module Code in Session table – Many to 1 relation to Training Module Table. EmployeeId - Many to 1 relation to Employee (many classes for 1 employee) Tbl 8 - Job Traning Job Function Id – Many to 1 relationship to Job Function Tbl (many classes to 1 function) Module Code – Many to 1 relationship to Training Module (many job functions to 1 class) Frequency – how often does this need to be re-trained. The above will answer the following questions: A list of employees, - what location they work at, - what they have been trained on - and who trained them - when they were trained While the above structure will allow you to determine the answers to the following questions, you will need to figure out how to: - determine what an employee still needs to be trained on, - determine when they are due to be retrained. I don’t know how to do this last part, but the above database structure should enable you to figure it out. You would link the employee table to the job function table, then link the job function table to the Job Traning table. This query would provide a list of all of the classes the employee should take. You would then link employee table to the Employee Training Table and this will give you a list of all of the classes the employee has taken. You will then need to figure out how to use these two queries to provide a report of classes still needing to be taken. I don’t know how to do that. Hopefully someone else will be able to provide assistance. Good luck Dennis |
Thread Tools | |
Display Modes | |
|
|