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