You say that your company is divided into groups and within each group are job titles. You also mention departments. Are departments equivalent to a group or a job title? Based on this statement
Some of the employees are cross trainied into different departments such as BURR/MILL.
It sounds like a department is equivalent to a job title. Is that correct?
Assuming that I am correct, let's start laying out the table structure starting with this statement:
For each job Title they cannot be in each department however some of the job descriptions can be. Our company is divided up into several groups such as
Quality - Engineering - Production - Purchasing - Management under each of these are a listing of job titles the only one that share a job title is our Qty / Eng Asst. Otherwise the rest of the job titles go under their own name.
tblGroups
-pkGroupID primary key, autonumber
-txtGroupName
tblJobTitles
-pkJobTitleID primary key, autonumber
-txtJobTitle
Since you have at least 1 title that is shared, you have to use the following structure to link the titles for each group:
tblGroupJobTitles
-pkGrpJobTitleID primary key, autonumber
-fkGroupID foreign key to tblGroups
-fkJobTitleID foreign key to tblJobTitles
A table to hold the basic employee information
tblEmployees
-pkEmpID primary key, autonumber
-txtFName
-txtLName
Since an employee can be trained in multiple jobs titles, you have to capture that using this structure
tblEmpJobs
-pkEmpJobsID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkGrpJobTitleID foreign key to tblGroupJobTitles
Now let's work with this statement:
Each skill is different with the exception of basic qualifications with this each employee is required to know the basic information on our company and that does not change but within each job description there are other requirements that are different from other jobs.
You need a table to hold all of the possible qualifications no matter what job they are for.
tblQualifications
-pkQualID primary key, autonumber
-txtQualDesc
Now since you have basic qualifications that are common to all jobs you have to tie those as well as the specific ones to each job
tblJobQuals
-pkJobQualsID primary key, autonumber
-fkGrpJobTitleID foreign key to tblGroupJobTitles
-fkQualID foreign key to tblQualifications
Now you have to link the employees achievement with each of the respective qualifications for their job
tblEmpJobQual
-pkEmpJobQualID primary key, autonumber
-fkEmpJobsID foreign key to tblEmpJobs
-fkJobQualsID foreign key to tblJobQuals
Since an employee can be assigned various ratings (basic, proficient, master) for each qualification over time, you have a one-to-many relationship
tblEmpQualEval
-pkEmpQualEvalID primary key, autonumber
-fkEmpJobQualID foreign key to tbEmpJobQual
-dteEval (evaluation date)
-fkEvalID foreign key to tblEvaluation
tblEvaluation (holds 3 records basic, proficient, master)
-pkEvalID primary key, autonumber
-txtEvalDesc
Now with regard to training, I'm not sure how to do this exactly, but I'm guessing that you need a table to hold all possible training courses
tblTraining
-pkTrainingID primary key,autonumber
-txtTrainingCourseName
-longFreq
You might include a frequency for those training courses that need to be taken on a regular basis. You can then use that to calculate the next time an employee needs to take the course
It sounds like certain types of training are associated with job titles, so you would join those similar to how I joined job titles with qualifications. And then you would link each required training to the employee. You would capture the date of the training for that employee assuming that an employee may undergo the training several times during their time in your employment.
You would include the stamp # in the table where you join the employee & the job. You would leave the field blank for those employees that do not have a stamp #
Hopefully this will be enough to get you started.
