John
To me, the easiest would be to use a VBA Function.
Public generateBillRate(Investment as Currency) as Currency
select case floor(Investment)
case < 0
generateBillRate = 0.0
case 0 to 10000
generateBillRate = 0.04 * Investment
case 10001 to 50000
generateBillRate = 0.03 * Investment
case > 50000
generateBillRate = 0.02 * Investment
end select
End Function
What this does is takes in the one value (Investment) and then does:
Look at the main value (The purpose of the 'floor(Investment)' is to look at the part to the left of the decimal part.
The Select then decides, based on the value, which of 4 ranges to apply.
If the value is negative, then we bill nothing.
If it is in one of two ranges, then we select the according rate, multiply it by the investment, and return that value
If it is above the 50,000, then we mulitply by the cheap rate and return that.
The beauty of a function is you can use it in other VBA coding or in a SQL Query. In the Query Builder, you can make a column
BillThem:generateBillRate(TotalInvestment)
(Or whatever your value of the total investment is called.)
Then, when your query runs, it will then run the function and put in the value as appropriate.
Good Luck
Paul