In an effort to transition a model over from Excel into Access, I've come across a roadblock. The formula I had to use to make this happen in Excel is a good 5 lines long, but I can't seem to find a way to make it happen in Access. Here's what I'm attempting to accomplish:
I've got a table listing usage by client (# users/month by client). What I'm trying to calculate is the per user fee. The per user fee are as such:
first 500,000 users = $1.00/user
next 500,000 users = $0.90/user
next 1,000,000 users = $0.80/user
Each client is generally not going to have more than a few thousand users each, which means the lower per user fee may not kick in until the 200th client or so. Because some clients are going to get charged a different fee than others, their order obviously matters. The order is assigned based on a date I'm referencing.
So..... the calculation ends up being "Sum the users for all clients that have a date earlier than my date, and if the users sum to less than 500,000, assign me a $1.00 per user fee. If the users sum to between 500,001 and 1,000,000, assign me a $0.90 per user fee. So on and so forth."
Is this possible??? I appreciate any help!
Regards,
David