Many to Many relationship

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 21 Jan 2005 11:57 GMT

Hi All,

Many to many relationship.

I have this many-to-many relationship: 2 primary tables, and a junction table.
2 primary tables:

tblSkillsDescription:(PK): SkillID
TblTraining courses:(PK): 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.

Reply to this Message

Jeff Boyce - 21 Jan 2005 13:36 GMT

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>

> Hi All,
>
[quoted text clipped - 30 lines]
> I have tried with referential integrity, but I always end stuck.
> Any help appreciated.

Reply to this Message