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 |
#11
|
|||
|
|||
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!* |
#12
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Clearly, I have no idea what you are talking about either.
If your tables have no foreign keys, you can do what you like. -- 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 ... 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. |
#13
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Of course they have foreign keys!
I can list all the employees on a given course by using a query. "Allen Browne" wrote: Clearly, I have no idea what you are talking about either. If your tables have no foreign keys, you can do what you like. -- 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 ... 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. |
#14
|
|||
|
|||
Many-2-many relationships: Can I be told ...
What foreign key do you have?
Does your Course table have an EmployeeID foreign key? Or does your Employee table have a CourseID foreign key? -- 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 ... Of course they have foreign keys! I can list all the employees on a given course by using a query. "Allen Browne" wrote: Clearly, I have no idea what you are talking about either. If your tables have no foreign keys, you can do what you like. "scubadiver" wrote in message ... 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. |
#15
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Simple example:
1:n relationship and create the following query. Name Course John A John B John C Sarah A Sarah C Sarah D Phil B Phil C Phil D I now know that Course A was attended by John and Sarah Course B was attended by John and Phil Course C was attended by John, Sarah and Phil Course D was attended by Sarah and Phil If there is anything wrong with this please let me know. "Allen Browne" wrote: Clearly, I have no idea what you are talking about either. If your tables have no foreign keys, you can do what you like. -- 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 ... 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. |
#16
|
|||
|
|||
Many-2-many relationships: Can I be told ...
You could do it all in one table:
course, course date, course leader, employee name, employee number, employee phone You could extract all of the information that you require from that one table. There would be a tremendous amount of data duplication. That is why we have relational databases. In such a relational database the course table has all the informations about a course. The employee has all of the information about the employee. and the link table has all of the information about the relationship between the employee and the course. It might have contain date enlisted, the fee paid, the student grade for that course. three tables. "scubadiver" wrote in message ... 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!* |
#17
|
|||
|
|||
Many-2-many relationships: Can I be told ...
How??? The relationship you describe is:
tblEmployees: EmployeeID (PK) EmployeeName tblCourses: CourseID (PK) CourseName EmployeeID (FK) Now tell me how many employees can be enrolled in the course with CourseID = 1? Where are you going to store all these extra EmployeeID's??? Dave "scubadiver" wrote: 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!* |
#18
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Now tell me how many employees can be enrolled in the course with CourseID =
1? I can have as many employees as I like! Obviously it means duplication of courses for each employee. Where are you going to store all these extra EmployeeID's??? Each employee info is already stored in the main table. "David M C" wrote: How??? The relationship you describe is: tblEmployees: EmployeeID (PK) EmployeeName tblCourses: CourseID (PK) CourseName EmployeeID (FK) Now tell me how many employees can be enrolled in the course with CourseID = 1? Where are you going to store all these extra EmployeeID's??? Dave "scubadiver" wrote: 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!* |
#19
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Course table has an employeeID foreign key
"Allen Browne" wrote: What foreign key do you have? Does your Course table have an EmployeeID foreign key? Or does your Employee table have a CourseID foreign key? -- 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 ... Of course they have foreign keys! I can list all the employees on a given course by using a query. "Allen Browne" wrote: Clearly, I have no idea what you are talking about either. If your tables have no foreign keys, you can do what you like. "scubadiver" wrote in message ... 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. |
#20
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Which is the foreign key of this table.
Do you really have a foreign key called "Name"? What happens when you have 2 employee with the same name? Do you also have fields in this table for the address of each person? So if someone attends 2 courses, you have to enter their address in 2 records? -- 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 ... Simple example: 1:n relationship and create the following query. Name Course John A John B John C Sarah A Sarah C Sarah D Phil B Phil C Phil D I now know that Course A was attended by John and Sarah Course B was attended by John and Phil Course C was attended by John, Sarah and Phil Course D was attended by Sarah and Phil If there is anything wrong with this please let me know. "Allen Browne" wrote: Clearly, I have no idea what you are talking about either. If your tables have no foreign keys, you can do what you like. -- 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 ... 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. |
Thread Tools | |
Display Modes | |
|
|