Calculation with several variables
The task here is to devise a formula that returns the proper 'day rate' based on two variables. There are four cottages (each is one record in the table 'cottages') with two day rates based on seasonality. Also a related 'Reservation' table that selects dates and which cottage. No problem returning the correct rate for the first cottage using
When selecting cottage 'c1' , 70 returns to dates prior to Apr 30 and 100 for dates after, correctly.
But for cottage 'c3' a different rate schedule is in effect .140 before Apr 30 and 200 after. I have tried using OR and repeating the expression with c3 and the different rates but the result is not correct and c1 no loger returns correctly either.
How can these expressions be strung together to produce the correct day rate when cottage c3 or c5 (also with a different rate schedule) is selected? Can it be done using only two tables?
Many Thanks for replies