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
|
|||
|
|||
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. |
#2
|
|||
|
|||
Select more than one
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. |
#3
|
|||
|
|||
Select more than one
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. |
Thread Tools | |
Display Modes | |
|
|