Many-2-many relationships: Can I be told ...

But that doesn't mean to say I can't list all the employees who have taken a
specific course? If establishing a 1:n relationship between course and
employee means it could be quicker to enter information into a form, then
there is a trade off.

"Allen Browne" wrote:

If you have a one-to-many relation between employee and course, you have an
EmployeeID field in your Course table. Therefore one employee can do
multiple courses, but any course can have only one employee.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"scubadiver" wrote in message
...
I realise each course can be run multiple times.

In my database, I have a "1:n" relationship between "employee" and
"course".
I can select in the "course subform" all the training courses that each
individual employee does.

whats wrong with that?



"Allen Browne" wrote:

The other important piece of the puzzle is that each course can be run
multiple times. Therefore employees enrol in an instance of a unit (e.g.
the
2006 instance of "Basic Firefighting"), not just a unit

Therefore you need:
Unit table: one record for each subject that an employee can do:
UnitID primary key

Course table: One record for every time a Unit is offered
CourseID primary key
UnitID foreign key to Unit.UnitID
StartDate when this instance of this unit begins.

Enrol table: One record for every student in a course.
EnrolID Primary key
CourseID Which couse the person enrolled in. (Defines Unit too.)
EmployeeID Who enrolled in this course.
Outcome Whether the employee met all criteria

Employee table: One record for each person
EmployeeID primary key

The Enrol table above resolves the many-to-many relationship between
Course
and Employee into a pair of one-to-many.

"scubadiver" wrote in message
...
...what I am missing?

If I have training courses and employees, I know that each employee
attends
many training courses and each course is attended by many employees.
That
I
can understand.

If I set up a "1:n" relationship between "employee" and "course" I will
know
by DEFAULT who attended what course. Since I am assuming that this is
the
purpose of having a "1:n" relationship between "course" and "employee"
doesn't this make the 2nd relationship completely redundant?

I could be entirely wrong ... *sigh!*