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 |
#31
|
|||
|
|||
Many-2-many relationships: Can I be told ...
What is the difference between "frmDept" in your database and a form that has
been designed using a 1:m relationship? "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!* |
#32
|
|||
|
|||
Many-2-many relationships: Can I be told ...
The frmDept form IS built on a "logical" 1:M relationship. It is a
relationship between the table "DEPT" and the query "zqryItemTransaction". (It is a "logical relationship" because no such physical relationship can exist between a table and query.) The point is that zqryItemTransaction is a Join of the other two tables (tblITEM and tblTRANS) in the M:M relationship. And that's how you implement a M:M relationship in a form. You base the main form one or the other of the main tables and base the subform on a Join of the other main table and the linking table. In the Training Registration database, I have two forms. 1) has the Student in the main form and a Join of the linking table and Course table in the subform and 2) the other has the Course in the main form and a join of the other two in the subform. That way you can enter data from either 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 ... What is the difference between "frmDept" in your database and a form that has been designed using a 1:m relationship? "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!* |
#33
|
|||
|
|||
Many-2-many relationships: Can I be told ...
This my current design:
tble_employee EmployeeID (PK) EmplName workstatus operations Dept Subdept Supervisor tble_course employeeID coursetype coursename trainingdate instructor I can see this is going nowhere because I still haven't been given a practical answer as to why a m:m relationship is any better. Maybe it is a lot simpler than I think it is but I can't see why. To give an example, in "ImplementingM2MRelationship.mdb" one of the forms consists of a main form (department) and a subform (transaction info and item info). The fields are from two different tables, so why is this necessary? I could be entering the same items for different departments? How is that different to having 1:m relationship? "Allen Browne" wrote: 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. |
#34
|
|||
|
|||
Many-2-many relationships: Can I be told ...
ok. Thanks.
Is it possible to use look up tables or combo boxes in a subform? "Roger Carlson" wrote: The frmDept form IS built on a "logical" 1:M relationship. It is a relationship between the table "DEPT" and the query "zqryItemTransaction". (It is a "logical relationship" because no such physical relationship can exist between a table and query.) The point is that zqryItemTransaction is a Join of the other two tables (tblITEM and tblTRANS) in the M:M relationship. And that's how you implement a M:M relationship in a form. You base the main form one or the other of the main tables and base the subform on a Join of the other main table and the linking table. In the Training Registration database, I have two forms. 1) has the Student in the main form and a Join of the linking table and Course table in the subform and 2) the other has the Course in the main form and a join of the other two in the subform. That way you can enter data from either 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 ... What is the difference between "frmDept" in your database and a form that has been designed using a 1:m relationship? "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!* |
#35
|
|||
|
|||
Many-2-many relationships: Can I be told ...
So there is no great advantage except that you can enter information in two
different ways? "Roger Carlson" wrote: The frmDept form IS built on a "logical" 1:M relationship. It is a relationship between the table "DEPT" and the query "zqryItemTransaction". (It is a "logical relationship" because no such physical relationship can exist between a table and query.) The point is that zqryItemTransaction is a Join of the other two tables (tblITEM and tblTRANS) in the M:M relationship. And that's how you implement a M:M relationship in a form. You base the main form one or the other of the main tables and base the subform on a Join of the other main table and the linking table. In the Training Registration database, I have two forms. 1) has the Student in the main form and a Join of the linking table and Course table in the subform and 2) the other has the Course in the main form and a join of the other two in the subform. That way you can enter data from either 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 ... What is the difference between "frmDept" in your database and a form that has been designed using a 1:m relationship? "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!* |
#36
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Roger has told me how a m:m relationship is implemented in a form which I
wasn't aware of. I have to say I still can't see any great advantage over a 1:m relationship apart from the ability to enter information using one of two different tables. "scubadiver" wrote: This my current design: tble_employee EmployeeID (PK) EmplName workstatus operations Dept Subdept Supervisor tble_course employeeID coursetype coursename trainingdate instructor I can see this is going nowhere because I still haven't been given a practical answer as to why a m:m relationship is any better. Maybe it is a lot simpler than I think it is but I can't see why. To give an example, in "ImplementingM2MRelationship.mdb" one of the forms consists of a main form (department) and a subform (transaction info and item info). The fields are from two different tables, so why is this necessary? I could be entering the same items for different departments? How is that different to having 1:m relationship? "Allen Browne" wrote: 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. |
#37
|
|||
|
|||
Many-2-many relationships: Can I be told ...
I'm not really sure how to respond to this, because several of us have
already said it a number of ways. But the ONLY way you can represent your data correctly is with a Many-to-Many relationship. And what you have created (by your table design) IS a Many-to-Many relationship. There is NO WAY you can use a One-to-Many relationship to accurately represent or store your data. So yes, there is a great advantage, because a Many-to-Many is the only one *possible*. -- --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 news So there is no great advantage except that you can enter information in two different ways? "Roger Carlson" wrote: The frmDept form IS built on a "logical" 1:M relationship. It is a relationship between the table "DEPT" and the query "zqryItemTransaction". (It is a "logical relationship" because no such physical relationship can exist between a table and query.) The point is that zqryItemTransaction is a Join of the other two tables (tblITEM and tblTRANS) in the M:M relationship. And that's how you implement a M:M relationship in a form. You base the main form one or the other of the main tables and base the subform on a Join of the other main table and the linking table. In the Training Registration database, I have two forms. 1) has the Student in the main form and a Join of the linking table and Course table in the subform and 2) the other has the Course in the main form and a join of the other two in the subform. That way you can enter data from either 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 ... What is the difference between "frmDept" in your database and a form that has been designed using a 1:m relationship? "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!* |
#38
|
|||
|
|||
Many-2-many relationships: Can I be told ...
"scubadiver" wrote in message
: Roger has told me how a m:m relationship is implemented in a form which I wasn't aware of. I have to say I still can't see any great advantage over a 1:m relationship apart from the ability to enter information using one of two different tables. The advantage of using a M:N relationship over 1:M relationship for what is actually a M:N relationship, is that the M:N relationship with a junction table actually works. 1:N does not. Please, do try - you will soon be able to confirm it. This has to do with table design - how to store information - not form design. You need to finish your table design before even thinking about the forms. Several have tried to create a mutual ground on which to explain your issues, here's another attempt http://r937.com/relational.html, see "One-to-Many Relationships" vs "Many-to-Many Relationships" (though the whole article is worth a read). You *DO* need the "Many-to-Many Relationships" version, given your initial requirement! "Two tables are related in a many-to-many (M—M) relationship when for every row in the first table, there can be many rows in the second table, and for every row in the second table, there can be many rows in the first table. Many-to-many relationships can't be directly modeled in relational database programs, including Microsoft Access. These types of relationships must be broken into multiple one-to-many relationships." Failure to do so, will first and foremost haunt you, and make it impossible for you to go to the pub with your collegues after regular hours - you will have to stay in the office to do maintenance, cause with such horrid structure, you will need everybody out of the db to be able to perform maintenance. Needless to say, such maintenance would be completely unnecessary with at good/correct design. You will also have to spend §!$@#¤'ve a lot of hours explaining why your system constantly gives wrong results, why it took ages to produce it in the first place, and why it's extremely difficult to add new features to it. But by all means, it's your job, and your reputation at stake, so do what you like. But, then you will probably come back and haunt us with more questions about stuff that would never happen if you'd established a correct database design in the first place ... cheers ;-) -- Roy-Vidar |
#39
|
|||
|
|||
Many-2-many relationships: Can I be told ...
On Fri, 10 Nov 2006 09:12:01 -0800, scubadiver
wrote: tble_employee EmployeeID (PK) EmplName workstatus operations Dept Subdept Supervisor tble_course employeeID coursetype coursename trainingdate instructor I can see this is going nowhere because I still haven't been given a practical answer as to why a m:m relationship is any better. Maybe it is a lot simpler than I think it is but I can't see why. To give an example, in "ImplementingM2MRelationship.mdb" one of the forms consists of a main form (department) and a subform (transaction info and item info). The fields are from two different tables, so why is this necessary? I could be entering the same items for different departments? How is that different to having 1:m relationship? The problem with your design is that if a given course has 24 employees registered, you must have 24 records in tble_course for that course. They all will have the same coursetype, coursename, trainingdate, and instructor. This wastes disk space, of course, since there is really only ONE course; with a many to many, you would enter these fields once and once only. The *real* problem though is that if you have 24 records, there is no way to be sure that the all DO in fact have the same coursename, trainingdate, and instructor. You could have a data entry error (typing the course date 24 times, wasting the data entry person's time) where there's a typo, and you have 23 employees coming on 11/10 and one showing up on 11/11 wondering why there's nobody there. Or your instructor might quit and need to be replaced; now you must track down 24 records to change the instructor, whereas with the many to many you make the change ONCE, in ONE record. If you really want to store information redundantly, you can. You're wasting storage, making your database less efficient, and - much worse - risking the integrity of your data. If that cost is irrelevant to you, go right ahead! John W. Vinson[MVP] |
#40
|
|||
|
|||
Many-2-many relationships: Can I be told ...
On Fri, 10 Nov 2006 09:18:02 -0800, scubadiver
wrote: Is it possible to use look up tables or combo boxes in a subform? ummmm.. Of course. Possible, routine, universal. Have you looked at the Northwind sample database Orders form? It's a perfect example of a many to many (Orders analogous to Employees, Products to Courses, OrderDetails the many to many relationship table). John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|