This should be easy, but I cannot seem to get it:

I have three tables - SalaryScale, Teacher and Courses

Fields in question:
SalaryScale: ScaleYear, ScaleStep, ScaleAmount
Teacher Fields: Teacher, TeacherStep
Courses: CourseYear, CourseTeacher, CourseHours, CourseCost

The ScaleAmount changes from year to year (the ScaleStep does not)
Typically the TeacherStep field does not change

I created a relationship TeacherStep::SalaryStep and thus I can see the current salary associated with that step
I also created a relationship Teacher::CourseTeacher and thus I can see the cost of the course through a calculation that will multiply the TeacherStep, and CourseHours

What I cannot figure out is how create a relationship based on the CourseYear and ScaleYear so that each course will accurately note what the SaleAmount was for that given year and the toal cost of the course for that given year. I can only seem to populate the current step and amount, regardless of the course year. 
 

I am mostly guessing here. If you want the record in Courses to get the ScaleAmount value based on both the CourseYear and the associated teacher's TeacherStep, you will have to add a TeacherStep field to the Courses table too. This could be either a calculation field = Teachers::TeacherStep, or a lookup. It must be a lookup if a teacher's step can change in the future.

Once you have that, you can define a relationship between Courses and SalaryScale as:

Courses::CourseYear = SalaryScale::ScaleYear
AND
Courses::TeacherStep =SalaryScale::ScaleStep

and get the ScaleAmount value from the related record in SalaryScale (there should be only one such related record, if I am not mistaken).

 

Edited August 11, 20169 yr by comment

That's it! Thank you very much