Results 1 to 4 of 4
  1. #1
    groonpooch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Location
    Montara, CA
    Posts
    2

    Complex "sumif" style formula

    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

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would create a table to hold the user fee rates

    tblUserFeeRates
    -pkUserFeeRateID primary key, autonumber
    -lngLowerUserLimit
    -currFeePerUser

    The lng prefix denotes a long integer number datatype and the curr prefix denotes a currency datatype.

    In this table, I would include another record in addition to the ones you mentioned. This last record would have unacheivably high number of users. I used 999999999 which is the maximum allowable number for a long integer number datatype field. If that is not big enough you may have to use another datatype for the field.

    What we would need to do is to pull the rate, the lower limit of the range and calculate the upper limit of the range.

    This can be done with a query that contains a nested query. The nested query gets the lower limit of the next range and then subtacts 1 from it to calculate the upper limit of the current range.

    The query would look like this (query name: qryRatesPerUser):
    SELECT tblUserFeeRates.pkUserFeeRateID, tblUserFeeRates.lngLowerUserLimit as LowerLimit, (SELECT Top 1 Q1.lngLowerUserLimit FROM tblUserFeeRates as Q1 WHERE tblUserFeeRates.lngLowerUserLimit<Q1.lngLowerUserL imit ORDER by Q1.lngLowerUserLimit ASC)-1 AS UpperLimit, tblUserFeeRates.currFeePerUser
    FROM tblUserFeeRates
    WHERE tblUserFeeRates.lngLowerUserLimit<999999999;

    Here is the approach that I would use for the rest of your issue.

    1. Create a query that pulls the list of users for "all clients that have a date earlier than my date" (query name: qryUserListByClient)
    2. Create an aggregate query based on the query in #1, have that query group by the client & count the # of users. (query name: qryCountOfUsersByClient)
    3.Create another query that uses the query from #2, so you should have the client and the number of users. Now you can either use a Dlookup() function or a nested query to pull in the rate for each client. The DLookup() or the nested query would use the user rate query I provided above. (query name: qryFinal)


    I've attached an example database with the tables and queries discussed above.

  3. #3
    groonpooch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Location
    Montara, CA
    Posts
    2
    jz,

    Thanks so much for the help. One thing I should have been clearer about is that I'm not sure your step one applies, as "all clients that have a date earlier than my date" is a statement that must be repeated with every record. Meaning, calculate the user fee for each client by summing the usage for all clients that have a date earlier than each client. Does that make sense?

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You have to select those records in the date range you want first otherwise your count will be off.

    Meaning, calculate the user fee for each client by summing the usage for all clients that have a date earlier than each client. Does that make sense?
    Not clear. Could you provide some example data?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-05-2011, 02:55 PM
  2. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  3. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  4. Complex Table "Lookup" Lists
    By Rawb in forum Access
    Replies: 19
    Last Post: 03-01-2011, 02:52 PM
  5. WHERE clause "too complex"
    By Ted C in forum Queries
    Replies: 4
    Last Post: 06-30-2010, 12:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums