Sorting based upon day of week, Relationship based upon day of week?
I need to be able to create a surgery schedule sorted by a surgeon’s block time on a particular day of the week. So I need the sort order to vary depending upon what day of the week it is.
For the purpose of my example, consider that I have two tables:
appointments, which has surgery appointment data including patient_name, surgeon_id, appt_date, and appt_time
surgeons, which has a surgeon_id and surgeon_name, etc.
The two tables are related via surgeon_id
On Monday, Surgeon A operates first in the morning, so I want her sort order to be 1. Surgeon B operates after Surgeon A on Mondays, so I want Surgeon B to be 2.
On Tuesday, Surgeon B operates first, so I want him to be 1, and Surgeon A is second, so I want her to be 2.
Currently, I am handling this in this fashion. The surgeons table has fields called “dr_order_monday” and “dr_order_tuesday” and so on. When I go to report on a day’s worth of appointments, I have created a report for each day of the week that has a sub summary part based upon that day of the week’s surgeon order field.
This works pretty well, but I want to create a bunch more layouts based upon this sort order and it seems too complicated. I don’t want to have to make 5 copies of each layout. Thus I would like to create a field in the appointments table that looks at the surgeons table via the day of the week, and then puts a number in the appointments table field corresponding to the proper sort order for that surgeon on that day of the week. Then after I have a found set of a day's worth of patients, I should only have to create one layout, which is sorted on that field.
My initial attempt was to create a calculated field called appointments.zz_c_sort_order that has a calculation like this:
DayName ( appt_date ) = “Monday" and surgeon = "Smith"; 1 ;
DayName ( appt_date ) = “Tuesday” and surgeon = "Smith"; 2 ;
I soon realized that I would need to do this for all 13 of my surgeons 5 times each (we work Mon-Fri mostly), and if they changed their order I would have to go back in and actually change the numbers in the calculation. This was not going to work.
I’m thinking of some sort of relationship between my appointments table and my surgeon table based upon the appointment day of the week, but I am getting strung out on it. Any good ideas? TIA!