AnsweredAssumed Answered

Calculation with several variables

Question asked by DavidGunter on Mar 14, 2013
Latest reply on Mar 14, 2013 by philmodjunk


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  

         Case(kf_cottageID="c1"and Arrives<Date(4;30;2013);"70";"100") 

     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