Combo Boxes - Many-to-Many

[alpinegroove]

Is it possible to use a form similar to the attached snapshot with combo boxes? Currently these fields are simple text boxes.

Can the fields under Instructor, TA, TA2, and Reader be turned into drop down menus that display all faculty members listed in tblPeople?

I have the following tables:
tblPeople


tblRoles
tblCourses
tblCoursePeopleRoles (junction table for the many-to-many relationship).

Or would the design of the form have to change to accommodate the use of combo boxes?
Would would be the best way to set this up?
I basically need a form that will allow me to designate which faculty be holding which role for each course.

Thank you.

[aytee111]

Yes that would make perfect sense.

If I may state an opinion, the table design maybe needs to be a bit more normalized.

Courses tbl - title, type, cap
People table - names of faculty
TA (whatever that is) - joined to the Peoples table
Lectures table - joined to courses table, including date/time, etc
Don't know where/how discussion fits in

One key item of normalization is to never repeat information (except keys/joins). Your instructor list and your lectures/discussions will then both be subforms.

[alpinegroove]

More information about the data model:

tblPeople
1, Johnson, Mike
2, Davids, Mary

tblRoles
1, Instructor
2, Teaching Assistant
3, Reader

tblCourses
1, Baking 101
2, Carpentry 200
3, Marketing Basics 1

tblCoursePeopleRoles (Field Order: Course, Person, Role)
1, 2, 1 (Baking 101; Davids, Mary; Instructor)
1, 2, 3 (Baking 101; Davids, Mary; Reader)
3, 1, 1 (Marketing Basics 1; Johnson, Mike; Instructor)
3, 2, 2 (Marketing Basics 1; Davids, Mary; TA)

Is this not normalized enough?

My main question had to do with whether the form in the snapshot, which is the form we used with a non-relational db, can be used with this model as well, and if so, how.

Thanks for your reply.

[aytee111]

Courses - combo box
Instructors - you only have space for two - if that is correct, then just change them to combo boxes, otherwise if more are required you can make it a subform