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 |
#21
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Yes, you can have as many employees as you like. But they can't each be
enrolled in the same course. You would have to create another course entry. If you look at the table structure your model suggests you would have: EmployeeID EmployeeName 1 Fred 2 Bob 3 Jim CourseID CourseName EmployeeID 1 Jumping 1 Now, you can't have Jim also enrolled in "Jumping" because there is nowhere to store his ID. Instead, you would have to create another "Jumping" course. CourseID CourseName EmployeeID 1 Jumping 1 2 jumping 3 Now, tell me how you are going to create a query that shows me all the employees that are enrolled in the Jumping course? Think of all the different ways a user could type "Jumping" which you're going to have to allow for in you query. Also remember that a course may have the same name, but be a different course. The whole point of using a relational database is to avoid duplication. By having to enter all that course information again, just to have many employees enrolled in it, is verging on insanity. Especially when creating a junction table to model the data properly is so easy. Dave "scubadiver" wrote: 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!* |
#22
|
|||
|
|||
Many-2-many relationships: Can I be told ...
If as David described, your data structure is:
tblEmployees: EmployeeID (PK) EmployeeName tblCourses: CourseID (PK) CourseName EmployeeID (FK) You can have multiple employees take the same course only if CourseID is not unique in tblCourses and you plan on duplicating course information. Hence, tblCourses should have no employee information and you need a junction table where CourseID and EmployeeID are both FK. scubadiver wrote: 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. How??? The relationship you describe is: [quoted text clipped - 44 lines] I could be entirely wrong ... *sigh!* -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200611/1 |
#23
|
|||
|
|||
Many-2-many relationships: Can I be told ...
So, once you put an EmployeeID into that field, the course is full?
You can have only one employee in the course? That's what happens with a ONE to MANY relation. -- 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 ... 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? "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. |
#24
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Do you really have a foreign key called "Name"? No, I said it is a simple
example. What happens when you have 2 employee with the same name? Each employee has a unique payroll ID number. The information for each employee is stored once. "Allen Browne" wrote: 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. |
#25
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Let me ask this, in your 1:n relationship, do you have Referential Integrity
enabled? -- --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 ... 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!* |
#26
|
|||
|
|||
Many-2-many relationships: Can I be told ...
I will have a look up table for course type and name so there is no variation.
The bit I am struggling with is entering the information into a form. I have created a simple example: tble_course CourseID (PK) Crse_type Crse_name tble_detail date instructor courseID (FK) EmployeeID (FK) tble_employee EmployeeID (PK) Empl_Name Based on an example I have seen, I could have one of two: (a) In the main form I can have EmployeeID and Name In the sub form I would have course type and name, date and instructor (b) In the main form I can have CourseID, type and Name In the sub form I would have employee Name, date and instructor At the moment my design is a 1:m and I have (a). So even if I have a m:m relationship and I choose (a) I will still be entering the same course for more than one employee. If I choose (b) I will be entering potentially the same names for each course. "David M C" wrote: Yes, you can have as many employees as you like. But they can't each be enrolled in the same course. You would have to create another course entry. If you look at the table structure your model suggests you would have: EmployeeID EmployeeName 1 Fred 2 Bob 3 Jim CourseID CourseName EmployeeID 1 Jumping 1 Now, you can't have Jim also enrolled in "Jumping" because there is nowhere to store his ID. Instead, you would have to create another "Jumping" course. CourseID CourseName EmployeeID 1 Jumping 1 2 jumping 3 Now, tell me how you are going to create a query that shows me all the employees that are enrolled in the Jumping course? Think of all the different ways a user could type "Jumping" which you're going to have to allow for in you query. Also remember that a course may have the same name, but be a different course. The whole point of using a relational database is to avoid duplication. By having to enter all that course information again, just to have many employees enrolled in it, is verging on insanity. Especially when creating a junction table to model the data properly is so easy. Dave "scubadiver" wrote: 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!* |
#27
|
|||
|
|||
Many-2-many relationships: Can I be told ...
I most certainly do. How could I not?
"Roger Carlson" wrote: Let me ask this, in your 1:n relationship, do you have Referential Integrity enabled? -- --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 ... 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!* |
#28
|
|||
|
|||
Many-2-many relationships: Can I be told ...
What you just described IS a Many-to-Many relationship. You have a Course
table and an Employee table with the Detail table acting as the linking table between them. As for implementing it in a form, on my website (www.rogersaccesslibrary.com), is a small Access database sample called "ImplementingM2MRelationship.mdb" which illustrates how to do this. There is also another sample called "TrainingRegistration.mdb" which shows it being used from both the Employee and Course perspective. -- --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 ... I will have a look up table for course type and name so there is no variation. The bit I am struggling with is entering the information into a form. I have created a simple example: tble_course CourseID (PK) Crse_type Crse_name tble_detail date instructor courseID (FK) EmployeeID (FK) tble_employee EmployeeID (PK) Empl_Name Based on an example I have seen, I could have one of two: (a) In the main form I can have EmployeeID and Name In the sub form I would have course type and name, date and instructor (b) In the main form I can have CourseID, type and Name In the sub form I would have employee Name, date and instructor At the moment my design is a 1:m and I have (a). So even if I have a m:m relationship and I choose (a) I will still be entering the same course for more than one employee. If I choose (b) I will be entering potentially the same names for each course. "David M C" wrote: Yes, you can have as many employees as you like. But they can't each be enrolled in the same course. You would have to create another course entry. If you look at the table structure your model suggests you would have: EmployeeID EmployeeName 1 Fred 2 Bob 3 Jim CourseID CourseName EmployeeID 1 Jumping 1 Now, you can't have Jim also enrolled in "Jumping" because there is nowhere to store his ID. Instead, you would have to create another "Jumping" course. CourseID CourseName EmployeeID 1 Jumping 1 2 jumping 3 Now, tell me how you are going to create a query that shows me all the employees that are enrolled in the Jumping course? Think of all the different ways a user could type "Jumping" which you're going to have to allow for in you query. Also remember that a course may have the same name, but be a different course. The whole point of using a relational database is to avoid duplication. By having to enter all that course information again, just to have many employees enrolled in it, is verging on insanity. Especially when creating a junction table to model the data properly is so easy. Dave "scubadiver" wrote: 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!* |
#29
|
|||
|
|||
Many-2-many relationships: Can I be told ...
So, your table has:
- a PayrollID number (foreign key to Employee.EmployeeID) - a CourseID (foreign key to Course.CourseID.) Since you have *2* foreign keys in this table, it is a junction table between 2 tables. This junction table is the standard way of resolve a many-to-many relation (between Employee and Course) into a pair of one-to-many relations (Employee to the junction table, and Course to the junction table.) -- 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 ... Do you really have a foreign key called "Name"? No, I said it is a simple example. What happens when you have 2 employee with the same name? Each employee has a unique payroll ID number. The information for each employee is stored once. "Allen Browne" wrote: 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? "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. |
#30
|
|||
|
|||
Many-2-many relationships: Can I be told ...
I've got the "ImplementingM2MRelationship.mdb" and I have looked at it but
when it comes to entering information. Maybe it is a lot simpler than I think it is but I cant get around the idea of dealing with two 1:m relationships simultaneously because that is how it feels to me. "Roger Carlson" wrote: What you just described IS a Many-to-Many relationship. You have a Course table and an Employee table with the Detail table acting as the linking table between them. As for implementing it in a form, on my website (www.rogersaccesslibrary.com), is a small Access database sample called "ImplementingM2MRelationship.mdb" which illustrates how to do this. There is also another sample called "TrainingRegistration.mdb" which shows it being used from both the Employee and Course perspective. -- --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 ... I will have a look up table for course type and name so there is no variation. The bit I am struggling with is entering the information into a form. I have created a simple example: tble_course CourseID (PK) Crse_type Crse_name tble_detail date instructor courseID (FK) EmployeeID (FK) tble_employee EmployeeID (PK) Empl_Name Based on an example I have seen, I could have one of two: (a) In the main form I can have EmployeeID and Name In the sub form I would have course type and name, date and instructor (b) In the main form I can have CourseID, type and Name In the sub form I would have employee Name, date and instructor At the moment my design is a 1:m and I have (a). So even if I have a m:m relationship and I choose (a) I will still be entering the same course for more than one employee. If I choose (b) I will be entering potentially the same names for each course. "David M C" wrote: Yes, you can have as many employees as you like. But they can't each be enrolled in the same course. You would have to create another course entry. If you look at the table structure your model suggests you would have: EmployeeID EmployeeName 1 Fred 2 Bob 3 Jim CourseID CourseName EmployeeID 1 Jumping 1 Now, you can't have Jim also enrolled in "Jumping" because there is nowhere to store his ID. Instead, you would have to create another "Jumping" course. CourseID CourseName EmployeeID 1 Jumping 1 2 jumping 3 Now, tell me how you are going to create a query that shows me all the employees that are enrolled in the Jumping course? Think of all the different ways a user could type "Jumping" which you're going to have to allow for in you query. Also remember that a course may have the same name, but be a different course. The whole point of using a relational database is to avoid duplication. By having to enter all that course information again, just to have many employees enrolled in it, is verging on insanity. Especially when creating a junction table to model the data properly is so easy. Dave "scubadiver" wrote: 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!* |
Thread Tools | |
Display Modes | |
|
|