Hi Student Databaser
Further to what Jeff said, one of the principles of database design is
creating the correct relationships between entities in your database. A
relationship is usually "one-to-many". For example, if you have a table of
Courses and a table if Instructors, each course is given by one instructor,
but each instructor can give many courses. The Instructors table is the
"one" side of the relationship and the Courses table is the "many" side.
Now, instead of storing all the information about the instructor in each
course record, you only need to store the value of the primary key of the
related record in the Instructors table, and all the other data can be
retrieved from there. This field in the "many-side" table is called the
"foreign key" in the relationship.
You probably knew all that already ;-)
OK, so for Courses and Competencies you have a "many-to-many" relationship.
One course can cover many competencies and one competency can be covered in
many courses. If you examine the last sentence, you see that the
many-to-many relationship is actually *two* one-to-many relationships. To
implement this, you need, as Jeff pointed out, a third table known as a
"junction table". The junction table (let's call it "CourseCompetencies")
comprises only two fields - a foreign key containing the value of a primary
key from Courses, and a FK containing the value of a PK from Competencies.
So your table structures look like this:
Courses:
--------
CourseID (PK)
CourseName
[Other course-related data fields]
Competencies:
-------------
CompetencyID (PK)
CompetencyName
CourseCompetencies:
--------------------
CourseFK (contains a CourseID value)
CompetencyFK (contains a CompetencyID value)
Your data might look like this:
CourseID: 1 CourseName: Introduction to Access
CourseID: 2 CourseName: Introduction to Excel
CompetencyID: 1 CompetencyName: Microsoft Office Applications
CompetencyID: 2 CompetencyName: Database Applications
CompetencyID: 3 CompetencyName: Spreadsheets
And for the junction table:
CourseFK: 1 CompetencyFK: 1 [Access is a MSOffice App]
CourseFK: 1 CompetencyFK: 2 [Access is a database app]
CourseFK: 2 CompetencyFK: 1 [Excel is a MSOffice App]
CourseFK: 2 CompetencyFK: 3 [Excel is a spreadsheet]
Now, the traditional way to display a many-to-many relationship in a form is
to have a main form bound to your primary table (in this case, Courses) and
a continuous subform bound to the junction table. On the subform, you have
a combo box bound to CompetencyID with its RowSource based on your reference
table (Competencies). Adding or removing a competency for a course is done
by adding or deleting a record in the subform.
Personally, I find this method rather clumsy, but I recommend you explore it
so that you have a thorough understanding of what is going on.
If you want a neater, more user-friendly method, I have written a sample
database demonstrating an easy way to manage many-to-many relationships,
using listboxes and combo boxes on a form. My colleague, Ken Snell, has
kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".
I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
