Form and subform with a many to many relationship

[Guest]

Hi All,

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.

 

[Jeff Boyce]

Chris

Already responded to in one of your other (newsgroup) posts. If you post
independently to multiple groups, responses don't show up across all groups
you've posted in. And it is rarely necessary to post in more than one -- it
means that folks may spend time addressing an issue that's already been
answered.

Check the newsgroup netiquette on crossposting/multiposting...

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,

The reason it has been posted in two different places is because I received
an error message from Microsoft saying that the first message could not be
posted for the time being.
Chris

 

[Ken->]

Jeff,
I'm interested in the answer to this question. I clicked the link you gave
but it just took me to his original post without any answers.
Ken

 

[Jeff Boyce]

Ah, that's happened to me, too.

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

I don't recall providing a "link". Have you Googled the topic to find his
other post?

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Chris

Hmmm? I can't find my reply, either -- maybe it was there and then gone,
caused by whatever shorted out for you. Let me take another look...

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

I suspect that you/users will be more interested in which classes support
which skills than the other way around. One way to handle this is to use a
mainform based on your skills, and a subform based on the classes. Pick a
skill on the main form, then pick all classes on the subform. Of course, if
you need to, you could do it the other way 'round.

Good luck

Jeff Boyce
<Access MVP>