Relationship Problems... Of The Database Kind

(see sig for details on what I'm capable of before you toss code at me ;P )


Here's my current setup:

Table1(Assignments)
Instructor <related to Instructor table, pulls data from there)
Academic Year
Fallcourse# (where # = 1, 2, 3, 4), (course pulled from Courses table)
Fallcourse#comp
Fallcourse#notes
Wintercourse#
Wintercourse#comp
Wintercourse#notes
Springcourse#
Springcourse#comp
Springcourse#notes

Table2(Courses)
Coursename (unique)
Coursetype (required, elective)
Courselevel (ugrad, grad, MAS)

Table3(Instructors)
lastname
firstname
empID
rank

The purpose of the database is to assign courses to various professors and append supplementary compensation and, if necessary, notes to each course assignment. Each professor can be assigned up to 4 courses (or something to take the place of a course) per quarter.

My problem. I have created a quite a few reports with this setup including an academic year schedule, compensation reports for the year, sabatical / course release listings, etc. I am having trouble with what I am hoping to be my final report.

Requirements for the final report:
List of all courses for an academic year (filter by year)
group by level (easy)
group by type (easy)
append instructor's name to the end

Report/Relationship Problem:

I can't seem to set the relationship to allow more than one field's data into the report. I can only show courses from the [Fallcourse1] because I can't relate multiple fields from the "Assisgnments" table to one field in the "Courses" table. Effectively, that's what I want to do, but my mind's burnt and can't seem to figure it out.

Maybe you guys could help me out? Suggestions, questions - all welcomed.