Originally Posted by
Mickjav
Thats Exel Not Access??
Correct! I used excel to make sure that I was getting the formulas right as I find it easier to see the data in there.
Here is the table I am using the query on:
I ended up with the following:
There are 3 cases:
1) using a combination of days, week, and months is the cheapest option
2) using a combination of only weeks and months is the cheapest option
3) using just months is the cheapest option
So in a query I made the following fields:
Code:
DaysInMonth: 31-(Day([OrderDate]+31)-Day([OrderDate])) //to calculate how many days are in the start month
Code:
DaysAway: DateDiff("d",[OrderDate],[ReturnedDate])+1 //to calculate how many days the equipment was away
Code:
CheapestCombo: IIf(([Combo1Total]<=[Combo2Total]) And ([Combo1Total]<=[Combo3Total]),1,IIf(([Combo2Total]<=[Combo1Total]) And ([Combo2Total]<=[Combo3Total]),2,IIf(([Combo3Total]<=[Combo2Total]) And ([Combo3Total]<=[Combo1Total]),3))) // to calculate the cheapest combination
Code:
Combo1Days: (([DaysAway] Mod [DaysInMonth]) Mod 7) //to see how many days in possibility 1
Code:
Combo1Weeks: Int(([DaysAway] Mod [DaysInMonth])/7) //to see how many weeks in possibility 1
Code:
Combo1Months: Int([DaysAway]/[DaysInMonth]) //to see how many months are in possibility 1
Code:
Combo1Total: [Combo1Days]*[DailyFee]+[Combo1Weeks]*[WeeklyFee]+[Combo1Months]*[MonthlyFee]+[SetupFee] //the total cost of possibility 1
Code:
Combo2Weeks: Int(([DaysAway] Mod [DaysInMonth])/7)+1 //how many weeks in possibility 2
Code:
Combo2Months: [Combo1Months] //how many weeks in possibility 2
Code:
Combo2Total: [Combo2Weeks]*[WeeklyFee]+[Combo2Months]*[MonthlyFee]+[SetupFee] //the total cost of possibility 2
Code:
Combo3Months: [Combo2Months]+1 //the months for possibility 3
Code:
Combo3Total: [Combo3Months]*[MonthlyFee]+[SetupFee] //the total cost of possibility 3
Code:
ChargedDays: IIf([CheapestCombo]=1,[Combo1Days],IIf([CheapestCombo]=2,0,IIf([CheapestCombo]=3,0,0))) //the number of days for the cheapest possibility
Code:
ChargedWeeks: IIf([CheapestCombo]=1,[Combo1Weeks],IIf([CheapestCombo]=2,[Combo2Weeks],IIf([CheapestCombo]=3,0,0))) //the number of weeks for the cheapest possibility
Code:
ChargedMonths: IIf([CheapestCombo]=1,[Combo1Months],IIf([CheapestCombo]=2,[Combo2Months],IIf([CheapestCombo]=3,[Combo3Months],0))) //the number of months for the cheapest possibility
Code:
ChargedTotal: IIf([CheapestCombo]=1,[Combo1Total],IIf([CheapestCombo]=2,[Combo2Total],IIf([CheapestCombo]=3,[Combo3Total],0))) //the cheapest total
This results in the query spitting out the cheapest combination of days, weeks, and months, as well as the final price to charge.