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 to Many relationship
Hi All,
Many to many relationship. I have this many-to-many relationship: 2 primary tables, and a junction table. 2 primary tables: tblSkillsDescriptionPK): SkillID TblTraining coursesPK): CourseID Junction table: TblAssessmentRecords. (PK): AssessRecordID The junction table contains the 3 primary Keys (PK): AssessRecordID, and 2 others from the 2 primary tables: SkillID CourseID Each primary table has a one-to-many relationship with the junction table. What I am trying to achieve in form and subform is the following: 1 given skill has many related training courses, and vice versa, 1 given training course can have many skills. Example: Electrical maintenance skill: - Voltage course 1 - Wires course - Work in tunnels I would like also to be able to use this for reports as well. So far I can achieve the look fine with the possibility to open the course list via a combo box but the database does not let me pick more than 1 choice. I have tried with referential integrity, but I always end stuck. Any help appreciated. |
#2
|
|||
|
|||
Chris
Not sure I can envision your form setup, but a 1:m relationship can be handled with a mainform/subform design. And although you describe the m:m relationship of skills and courses, I suspect that for data entry purposes, you want to show the courses related to the skills, rather than the other way around. No matter though, pick one direction and set up your mainform/subform to handle that. You will still be recording the m:m info, which you can use for queries, other forms and reports. Good luck Jeff Boyce Access MVP "Chris" wrote in message ... Hi All, Many to many relationship. I have this many-to-many relationship: 2 primary tables, and a junction table. 2 primary tables: tblSkillsDescriptionPK): SkillID TblTraining coursesPK): CourseID Junction table: TblAssessmentRecords. (PK): AssessRecordID The junction table contains the 3 primary Keys (PK): AssessRecordID, and 2 others from the 2 primary tables: SkillID CourseID Each primary table has a one-to-many relationship with the junction table. What I am trying to achieve in form and subform is the following: 1 given skill has many related training courses, and vice versa, 1 given training course can have many skills. Example: Electrical maintenance skill: - Voltage course 1 - Wires course - Work in tunnels I would like also to be able to use this for reports as well. So far I can achieve the look fine with the possibility to open the course list via a combo box but the database does not let me pick more than 1 choice. I have tried with referential integrity, but I always end stuck. Any help appreciated. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Deleting a foreign key relationship in SQL | Stevio | Running & Setting Up Queries | 2 | December 22nd, 2004 02:51 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Relationship not showing up in relationship view | Tom | Database Design | 10 | August 10th, 2004 05:01 PM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |