I am having a horrific time trying to calculate the pay rate for adjunct faculty. Each employee (table 1) is paid according to a multiple of a pay rate (table 2) for each class they teach (table 3).
The scale is set up so that after they teach THREE semesters, the employee moves up one level on the scale (which doesn't necessary start at 1). The semesters do not have to be consecutive so long as they teach at least one class.
Ex.
Employee Pay Rate Class Semester Note
Sue Smith 1 CHEM Fall 2003 (First Time at this rate)
Sue Smith 1 BIOG Fall 2003 (Same Semester as above)
Sue Smith 1 CHEM Spring 2004 (Second Time at this rate)
Sue Smith 1 CHEM Fall 2005 (Third Time at this rate)
Sue Smith 2 CHEM Fall 2006 (First Time at next rate)
Juan Woo 5 PHYS Spring 2004 (Second Time at this rate)
Juan Woo 5 PHYS Summer 2005 (Third Time at this rate)
Juan Woo 6 CHEM Fall 2006 (First Time at next rate)
Any suggestions on how this should be set up? I would ideally like the database to populate the pay rate based on the number of taught semesters.
The scale is set up so that after they teach THREE semesters, the employee moves up one level on the scale (which doesn't necessary start at 1). The semesters do not have to be consecutive so long as they teach at least one class.
Ex.
Employee Pay Rate Class Semester Note
Sue Smith 1 CHEM Fall 2003 (First Time at this rate)
Sue Smith 1 BIOG Fall 2003 (Same Semester as above)
Sue Smith 1 CHEM Spring 2004 (Second Time at this rate)
Sue Smith 1 CHEM Fall 2005 (Third Time at this rate)
Sue Smith 2 CHEM Fall 2006 (First Time at next rate)
Juan Woo 5 PHYS Spring 2004 (Second Time at this rate)
Juan Woo 5 PHYS Summer 2005 (Third Time at this rate)
Juan Woo 6 CHEM Fall 2006 (First Time at next rate)
Any suggestions on how this should be set up? I would ideally like the database to populate the pay rate based on the number of taught semesters.
