Autopopulate fields based on selection from previous field

[Michael_Randall]

Sorry, I posted this in Access Macros first.

I am trying to autopopulate a field in a table based on a selection in the
previous field. Here are my tables:

CLASS(ClassID(PK),CourseNumber(FK),InstructorID(FK))
COURSES(CourseNumber(PK), CourseName)
INSTRUCTORS(InstructorID, FName, LName)
INSTRUCTORCOURSES(InstructorID,CourseNumber) - junction table with composite
PKs

I've already filled out the INSTRUCTOR, COURSE, and INSTRUCTORCOURSES tables
and their relationships/ref integrity are already set up in Access. What I
would like to do is, in the CLASS table select a CourseNumber and based on
that CourseNumber, the InstructorID field is populated with a drop-down list.

Currently, I'm not using a Lookup for the CourseNumber in the table CLASS.
I'm considering basing that field (row source) on a query or the COURSE
table. I'm open to other solutions.


First, is this possible? Second, is this good database design? Third, how do
you do it, if you can?

 

[Allen Browne]

Could there be any case where different people in one class have different
instructors?

- No: Then the design you have is wrong, and could lead to bad data
(students wrongly assigned to an instructor.)

- Yes: Then the design you has is correct, so you can assign different
instructors to different students in the same class.

Perhaps we can discuss the other aspects of your question when we know your
choice here.