On Sep 24, 2:31*pm, Jamie Dickerson
wrote:
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. *

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)