Training Records Database

[Jamie Dickerson]

I am trying to set up a database for employee training records. So far I
have set up the tables I will need:

Work Instructions : Lists all (200+) work instructions, which serve as our
main training tool, the document# serves as the primary key and 3 fields to
list the departments the work instruction relates to (most belong to more
than 1 dept. but no more than 3)

Employees: Employee # (primary key), name, and fields to list their relative
departments (all employees belong to multiple departments (up to 15).

Departments: List of departments with an autonumber field for primary key

Training Grid: Lists employee, document #, date trained and a hyperlink
field linking to a signautre sheet.

I would like to be able to create a report that shows who needs to be
trained based on the relative department and the documents assinged to it.
The problem I am running into is while the documents will only belong to up
to 3 departments, the employees may belong to all of them. The way the
tables are set up I cannot show this relationship. What would be a better
way to do this, if any? Any suggestions at this point would be helpful. I
consider myself an Access Novice so please keep any suggestions relatively
simple.

 

[KARL DEWEY]

Suggestion - Do not use separate fields in instruction and employee tables to
indicate deparatments. Use another table.

Question - Do you have any re-occuring training such as Safety, Security,
Ethics, Sexual Harassment, etc? If so, then you need a field indicating
training cycle - annual (12 months), semiannual (6 months), quarterly (3
months), etc. Then you probably need training history (a separate record for
each time employee was trained on a given subject). Would you schedule based
upon last time trained or last time scheduled? Does everyone in a given
department require all training are do some, based on extra duties, need
subjects that others do not?

I was traing database manager for a 1000 employee organization and had a
query that created training records (employee-subject) for every topic of the
organization element they were assigned. A report was sent to the supervisor
to mark off any that was applicable to that person so the records could be
removed. In this way no one forgot to assigned any complusory training.

Any certifications required? We also recorded degrees and any speciality
training and skills even though not needed just in case the company might
have an immediate necessity.

 

[Jamie Dickerson]

I have a table which list the various departments. Should I create another
table which lists the employee and their department(s)? I am unclear on how
the relationship between the departments, employees and WI can be
illustarated? As I said the WI will belong to up to 3 departments, while an
employee could belong to 15 (based on cross-training, seniority, etc).

The purpose of the database is for ISO compliance. We previously used an
Excel spreadsheet, which was sufficient, but I perfer a database because of
the reporting and linking capabilites.

My training would be in regards to on-the-job training only, most of which
is machine or department specific. The training schedule would be based on
revisions to current work instructions or the creation of a new process. I
have already planned for this by listing the revision number on the WI table
along with a date field to show when it was last revised. I will check
revisions/updates monthly. Any department relating to the revised WI will be
retrained. As I am the only person who revises/creates WI I will manually
update the table as needed.

The set-up I have so far would work, but as I stated, I was hoping to run a
report that would show John Doe in Department A needs trained on the
following WI related to Dept A.

Thank you for your quick response. I will continue to research, and
experiment while I await your expertise.

 

[Piet Linden]

I am trying to set up a database for employee training records.  So farI
have set up the tables I will need:

Work Instructions : Lists all (200+) work instructions, which serve as our
main training tool, the document# serves as the primary key and 3 fields to
list the departments the work instruction relates to (most belong to more
than 1 dept. but no more than 3)

Employees: Employee # (primary key), name, and fields to list their relative
departments (all employees belong to multiple departments (up to 15).

Departments: List of departments with an autonumber field for primary key

Training Grid: Lists employee, document #, date trained and a hyperlink
field linking to a signautre sheet.

I would like to be able to create a report that shows who needs to be
trained based on the relative department and the documents assinged to it..  
The problem I am running into is while the documents will only belong to up
to 3 departments, the employees may belong to all of them.  The way the
tables are set up I cannot show this relationship.  What would be a better
way to do this, if any?  Any suggestions at this point would be helpful..  I
consider myself an Access Novice so please keep any suggestions relatively
simple.  

Somewhat depends on your rules, but I wrote a training DB like this
once... the basic rule was that there were some "Trainings" that were
required for everybody, and some for only certain departments.

Here's the Diagram:

Employee--(1,M)--Training---(M,1)--WorkInstruction

Employee--(1,M)---EmployeeDepartment---(M,1)---Department

Department--(1,M)---RequiresWI----(M,1)---WorkInstruction

Once you have all those joined, you can join all your tables, write
your queries and build your reports.

Make sure everything is working with *minimal* data (like 2-3 records
per table), because you will need to "proof" your answers by doing
them manually. Once you're sure everything is working, add the rest
of the data.

Primary Keys
Employee (EmployeeID)
Training(EmployeeID, WI_ID)
WorkInstruction(WI_ID)
EmployeeDepartment(EmployeeID, DepartmentID)
Department (DepartmentID)
RequiresWI (DepartmentID, WorkInstructionID)
WorkInstruction(WorkInstructionID)