too much data

[Lori2836 via AccessMonster.com]

Good morning. I have been asked to create a database for training. There
are a total of 495 different work instructions, 129 employees. Each
employee could be trained on a work instruction at different times throughout
the year....rarely the same time. And with each work instruction they are
asking to have 4 columns added. One for training date, revision, revision
date and legend. They are looking to be able to go into a table or form and
click a checkbox for who will need to trained on each work instruction and
then be able to query to show only those that need training. I'm not sure
how to proceed. I have a table of Work Instructions (including each extra
column needed) and a table of Employees with pertinent info such as position,
hire date, etc. How do I create this monster? How would/could I connect
these two table to come up with what is being requested?

Thanks. I've not been able to get any help so far that makes any sense to me!

 

[John W. Vinson]

Good morning. I have been asked to create a database for training. There
are a total of 495 different work instructions, 129 employees. Each
employee could be trained on a work instruction at different times throughout
the year....rarely the same time. And with each work instruction they are
asking to have 4 columns added. One for training date, revision, revision
date and legend. They are looking to be able to go into a table or form and
click a checkbox for who will need to trained on each work instruction and
then be able to query to show only those that need training. I'm not sure
how to proceed. I have a table of Work Instructions (including each extra
column needed) and a table of Employees with pertinent info such as position,
hire date, etc. How do I create this monster? How would/could I connect
these two table to come up with what is being requested?

Thanks. I've not been able to get any help so far that makes any sense to me!

What you have here is a very common situation: a "many to many" relationship.
Each Employee deals with many Instructions; each Instruction applies to many
Employees.

Whenever you have a many to many relationship in Access (or any other
relational database) you *need a new table* to link them. Each record in this
new table records the fact that an individual employee received an individual
instruction.

So you'll have tables like:

Employees
EmployeeID <Primary Key>
LastName
FirstName
Department
HireDate Date/Time
<other employee data, *nothing* about instructions>

Instructions
InstructionNo <Primary Key>
Legend
<information about the instruction>

InstructionsReceived
EmployeeID <link to Employees, who got the instruction>
InstructionNo <link to Instructions, what they got>
TrainingDate <when they got it>
Comments <how they did, etc.>

I'm not sure whether Revision and RevisionDate apply to an instruction as a
whole, or to one individual's instance of the instruction.

Rather than a checkbox, use a Subform based on the InstructionsReceived table.
You could have two different forms - one with the mainform based on Employees,
with a subform based on InstructionsReceived; the subform would use the
EmployeeID as the master/child link field and contain a combo box allowing the
selection of an instruction. You could also have a Form based on Instructions,
with a subform based (again) on InstructionsReceived, this time using the
InstructionNo as the master/child link; the subform would have a combo box
bound to EmployeeID but displaying the employee's name. This latter form would
let you show all employees who have received a given instruction.

John W. Vinson [MVP]