Results 1 to 3 of 3
  1. #1
    JDA2005 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11

    Programming Adjustable Pricing Fees

    My apologies if this is the wrong section.



    I am currently building a system for an investment firm that rates their clients individually based on total investments.

    Currently they are using an Excel sheet to calculate how much should be charged to each client, however Excel doesn't automate this.

    What I need help figuring out is if it is possible within access to be able to enter in a total investment and have the system calculate different rates for different total variations.

    For example:
    $1-$10,000,000 = fee of 4%
    $10,000,000 - $50,000,000 = fee of 3%
    $100,000,000+ = fee of 2%

    Thanks for any help that can be provided.

    John

  2. #2
    pdouglas is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Jun 2009
    Posts
    12

    Use a VBA Function

    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

  3. #3
    JDA2005 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11
    You definitely started me off in the right direction, but I think this can be done with IIF statements hopefully.

    Going to start a new thread with a clearer title.

    Thanks for the help.

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

Similar Threads

  1. New to Access programming
    By pushpm in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 03:03 PM
  2. Access 2000 programming startup options
    By nosaj_ccfc in forum Programming
    Replies: 2
    Last Post: 10-17-2008, 02:18 PM
  3. Programming Language like Access
    By cwf in forum Programming
    Replies: 2
    Last Post: 05-17-2008, 03:02 AM
  4. Access Programming and Access Data Page
    By frmdread67 in forum Programming
    Replies: 0
    Last Post: 03-19-2008, 03:11 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