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

I know it is a many-to-many relationship. In principle I can see that a
junction box IS required. I'm not disagreeing with you but I still haven't
been given a convincing *practical* (and I emphasise the word "practical")
argument as to why it is required.

Asking the question differently, If I also create a 1:n relationship between
course and employee what practical advantage does that have for form design
and data entry?

If that question can be answered for me, I would be happy!

"tina" wrote:

yes, i followed that thread. the issue is not how clear your descriptions
are, hon. you seem to think that if you explain your entities enough,
someone will tell you that they are not a many-to-many relationship and you
don't need a junction table. but the fact is that employees and courses IS a
many-to-many relationship, and you DO need a third, junction table to model
that relationship.

hth


"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3506A329-27D4-43D6-97B9-7F75727FF12A@xxxxxxxxxxxxxxxx
It isn't as though we have set annual time tables like a professional
training organisation would have. The training where I work is done on an
ad-hoc basis and whenever its required so I will include a date field to
say
when the employee did that course.

I had a discussion with BruceM which turned rather heated but it was my
fault for not being clearer than I should have been.

"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.

--
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" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3CA2E8B5-1EFD-456F-A353-F4F521401BCB@xxxxxxxxxxxxxxxx
...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!*






.

Relevant Pages

  • Re: Relationship problem
    ... In general you would add a JobID to the Groups table, ... junction table as a subform to that form, but you will only be able to view ... EmployeeID (primary key) ... You would typically have a main form based on either the Employee table ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relationship problem
    ... junction table as a subform to that form, but you will only be able to view ... EmployeeID (primary key) ... GroupID ... You would typically have a main form based on either the Employee table ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relationship problem
    ... junction table as a subform to that form, but you will only be able to ... EmployeeID (primary key) ... GroupID ... You would typically have a main form based on either the Employee ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Junction table question
    ... > minute unscheduled session at a supervisor's discretion. ... > the training that each employee has received. ... > I have an employee table, a training session ... > table, and a junction table, ...
    (microsoft.public.access.gettingstarted)
  • Re: Requery Issue
    ... > ReQuery is now displaying the correct info. ... Employee record *before* adding the EmployeeCourseJoin records ... >> not the junction, so it should not be in that table at all. ...
    (microsoft.public.access.formscoding)
Loading