Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    5

    Unanswered: expert calculation

    Hi;

    I have a table and fields are as;

    a1: 15000
    aper : 15%
    b1 : 25000
    bper : 12%
    c1: 50000
    cper : 8%
    d1: 140000
    dper : 6%
    e1: 370000
    eper : 4%
    f1: 550000
    fper : 2%
    g1: 950000
    gper : 1%

    And i have a value, example.. 45000 USD
    45000 bigger than a1
    So, for first fields, a1 * aper = 1800 usd
    45000 -15000 = 30000
    30000 still bigger than b1(25000)
    so, b1 * bper = 2500 usd

    now 45000- 15000 -25000 = 5000 less or equal than c1 (50000)

    5000 * cper = 400 usd

    Result : 1800 + 2500 + 400 = 4700 USD ' i want this result

    ------------------------------------------

    so another example;

    i have a value, 16000 usd

    a1 * aper = 1800 usd

    16000-15000 = 1000 usd

    1000 * bper = 100

    result: 1800 + 100 = 1900 USD

    -----------------------------------
    another example
    i have value, 5000 usd
    5000 * aper = 600 usd
    result = 600 USD

    ________________________________________
    But if result < 140 usd than result = 140 usd


    How can i get this result from query or ado, dao ??
    Thank you...
    Last edited by oblivion; 05-14-08 at 03:55.

  2. #2
    Join Date
    May 2008
    Posts
    5
    Another examples;

    Value -------Result
    48.000 ---> 4.940 USD

    65.000 ---> 6.300 USD

    160.000 ---> 12.500 USD

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I know of no way to do this other than to code the logic into a VBA function.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2008
    Posts
    5
    Ok, but how a VBA function?

    Thanks again..

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ever heard of google?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2008
    Posts
    5
    i now how create public function but i can not prepare formul for that results!!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you can create a function do
    Id suggest you create your function supplying the value, and return the result of your calculation

    call it as part of a SQL slect eg
    select mycolumn1, mycolumn2, MyfucntuionName(mycolumn3) from mytable

    how you implement your business logic is up to you.. Id suggest a select case would be the best option.

    failing that you are going to have to invent some fiendishly complex SQL statement.. in fact you could implement your calculation in the function usign SQL.. but I wouldn't want to do that in an inline query, as the performance hit could be horrific.. it would be fine in a for or report where you'd only have to open the connection once, but in an inline query it could be a performance killer
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2008
    Posts
    5
    Ok, I will try, but as hard

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nobody said database development was easy
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •