Select more than one

I currently have a field labeled as competences. It is used to show which
competencies are association with a certain course. Since there are more
than 1 competency, it is a text field and the competencies listed in this
field are separated by commas. Is there a way to have a drop down menu where
you can select more than 1 answer? Or should i have multiple fields instead?

Also, is there a way to add logic such as if field A=1then field B is
displayed, and if field A=2, field C is displayed? Should this just be done
in a form, and the table would have a lot of fields?

I'm having trouble wrapping my head around this one.

Thanks for your consideration.

If you use one field for each competency, you have a spreadsheet!

Access is a relational database. If you are saying that one 'course' can
have one or more 'competencies' associated with it, you need a table for
courses, a table for competencies, and a third table to hold valid
combinations of course X compentency.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Student Databaser" wrote in
message ...
I currently have a field labeled as competences. It is used to show which
competencies are association with a certain course. Since there are more
than 1 competency, it is a text field and the competencies listed in this
field are separated by commas. Is there a way to have a drop down menu
where
you can select more than 1 answer? Or should i have multiple fields
instead?

Also, is there a way to add logic such as if field A=1then field B is
displayed, and if field A=2, field C is displayed? Should this just be
done
in a form, and the table would have a lot of fields?

I'm having trouble wrapping my head around this one.

Thanks for your consideration.

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



"Student Databaser" wrote in
message ...
I currently have a field labeled as competences. It is used to show which
competencies are association with a certain course. Since there are more
than 1 competency, it is a text field and the competencies listed in this
field are separated by commas. Is there a way to have a drop down menu
where
you can select more than 1 answer? Or should i have multiple fields
instead?

Also, is there a way to add logic such as if field A=1then field B is
displayed, and if field A=2, field C is displayed? Should this just be
done
in a form, and the table would have a lot of fields?

I'm having trouble wrapping my head around this one.

Thanks for your consideration.