If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many-2-many relationships: Can I be told ...
....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!* |
#2
|
|||
|
|||
Many-2-many relationships: Can I be told ...
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" 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!* |
#3
|
|||
|
|||
Many-2-many relationships: Can I be told ...
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. -- 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 ... ...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!* |
#4
|
|||
|
|||
Many-2-many relationships: Can I be told ...
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!* |
#5
|
|||
|
|||
Many-2-many relationships: Can I be told ...
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" 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!* |
#6
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Im being quite presumptious because the user won't get feedback from the
managers until next week! umm... "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!* |
#7
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Any 1:M relationship can be written in plain English in two sentences, one
for each direction. Like this: Each Employee can take One or More Courses Each Course can be taken by One And Only One Employee This is what a One-To-Many relationship means, so by definition, if you create a 1:M relationship, only one employee can take any given course. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "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!* |
#8
|
|||
|
|||
Many-2-many relationships: Can I be told ...
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" wrote in message ... 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" 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!* |
#9
|
|||
|
|||
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" wrote in message ... 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" 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!* |
#10
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Not necessarily.
With just a 1:n relationship between employee and course not only can I select multiple courses for one employee, I can also select the same course for multiple employees. "Roger Carlson" wrote: Any 1:M relationship can be written in plain English in two sentences, one for each direction. Like this: Each Employee can take One or More Courses Each Course can be taken by One And Only One Employee This is what a One-To-Many relationship means, so by definition, if you create a 1:M relationship, only one employee can take any given course. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "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!* |
Thread Tools | |
Display Modes | |
|
|