Multiple fields filled with IDs from same source

[nate]

I have an enrolment database to put students in classes. On the class setup form, there are three combo boxes, to select Teacher1, Teacher2 and Teacher3 (classes have multiple teachers, but never more than 3), which all come off tblTeachers.

The combo-box records the TeacherID on the tblClasses, under Teacher1ID, Teacher2ID and Teacher3ID fields.

However, I have encountered a few problems with this, 1) the report that generates the roster, comes from a query that collects the students that have enrolled in the class. However, I can't figure out how to change the TeacherIDs into the teacher names, I don't know if it could be solved with a DLookup, or if my class table is just poorly laid out, and if I need another separate table.

Does anyone have any ideas about how I could fix this?

 

[spoole]

I may have miss understood you but I assume you have the teachers names in tblTeachers, if so add that table to the query (the query you base your report on for the report) and join by TeacherID to Teacher1ID, then add that table again and join by TeacherID and Teacher2ID and so on.