Jump to content

Year and Salary relationship


crazybake
 Share

This topic is 1814 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. 
 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 1814 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.