I've been working on an Access 2007 database for a bit now, but I can't seem to get past this one problem. I'm pretty new to Access, so I might just be doing things wrong... but so far, I've tried making junction tables and different types of joins/relationships and subsheets and none of these things have solved the problem that I have.
The database I'm working with contains three separate sheets: Classes, Instructors, and Students. These come with the primary keys Class Number, Instructor ID, and Student ID respectively. In the Students sheet, I have the class numbers, one for each class the student goes to. In the Instructors sheet, I have the classes they teach, again one for each class they instruct. I'll note that because students and teachers often have more than one class, they have more than one field in the database (Class Num 1, Class Num 2, etc.) The Classes sheet contains all of the information for the classes, and their Class Numbers.
I want to be able to make queries based on the students' classes. For example, I want to be able to find all of the students in one class number, and their instructors. Each query I've made has been missing data. I'm out of ideas for how to fix it, so I'm hoping someone here can help me!