Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8

    Unanswered: Math problem within query

    Hi
    I have a rather complex math equation I need to run via a query. I also need help with best way to design tables to suit this query but the equation is my biggest problem

    Basically it works like this, I have an invoice DB that returns final billing amount for our drilling company. We charge as follows

    PQ3 (size of hole) 0-100 metres = $75 per metre, 100-200 metres = $82 per metre, 200-300 metres = $89 per metre and 300 metres plus = $93 per metre

    So, if a hole reaches a depth of 237 metres, then the first 100 metres is billed at $75 per metre ($7500.00) then from 100 to 200 metres is billed at $82 per metre ($8200.00) and then from 200 to 237 metres is billed at $89 per metre ($3239.00) but how the heck do I put this in an expression within my query.

    I have a table which has the following feilds
    Start PQ3
    End PQ3
    EOH (End of hole)

    The table then looks like this for the above example
    Start PQ3 = 0
    End PQ3 = 237
    EOH = 237

    There are also other sizes involved and some holes are drilled starting out one size but then changing mid way to anotyher size, these are all billed at different rates.

    Please, anyone with a math head can you help me. I'm going round the twist trying to get this thing working!


    Thanks heaps in advance (praying someone does!)
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select
    iif(PQ3 <= 100, PQ3*75, 7500 +
    iif(PQ3 <= 200, (PQ3-100)*82, 820 +
    iif(PQ3 <= 300, (PQ3-200)*89, 890 + (PQ3-300)*93)))
    from invoices


    rudy

  3. #3
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8
    Hi Rudy

    Thanks for the reply but I'm not sure that would work as the rate are variable between jobs, that means I would have to re-do the query for each job.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, i answered the question that was asked

    if you want to introduce variable rates, would they be in another table?

    did i at least give you a hint as to how to construct a nested IIF?


    rudy

  5. #5
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8
    Rudy

    Well I guess you did. I have had some other advice that suggests I may be better off using modules for this problem. Now I'm just more confused than I started. I do appreciate your help though.

    Maybe I worded the question wrong, I will re-post this again in a short while, I just woke up so I'm in need of a coffee & shower ... maybe my head will work better then LOL

    I'll re-do the DB and post a smaller version of it with full tables and criteria which may help to show exactly what I need. In the meantime thatnks again for your attention.

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    hmmm

    What about to use Function? It's slow for large queries (thousands of records)....


    1. I created Public Function, which calculates exactly what you want. The input parameters are DEPTH and rates, the output is the price....

    2. I created your query....

    3. send some money to http://www.isc.org or at least think about it, please. They think about you too!


    Jiri
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Select
    iif ( PQ3 <= 100, PQ3*[rates]![FirstHun],
    [rates]![FirstHun]*100+ iif ( PQ3 <= 200,[rates]![SecHun]*(PQ3-100),[rates]![SecHun]*200+[rates]![SecHun]*(PQ3-200)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    hmmm

    Guys,

    Do you really read the task? You cannot calculate it your way! IT'S WRONG!


    135m is
    100 * 75 + 35 * 82

    NOT 135 * 82


    look at the function I sent yesterday!


    Jiri

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jiri, calm down

    m.timony and i both submitted solutions that actually do work, if you had bothered to understand them

    the IIF conditions are nested, not sequential, and act like a three-level if/else tree

    they do, in fact, properly evaluate 135 as 7500 + 35*82


    rudy

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    my solution is your solution i've just added the variable prices
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: hmmm

    i'll walk you through it
    Code:
    Select
    iif ( PQ3 <= 100, PQ3*[rates]![FirstHun],
    [rates]![FirstHun] * 100+ iif ( PQ3 <= 200,[rates]![SecHun] * (PQ3-100),
    [rates]![SecHun] * ##typo##100+[rates]!##typo## [restHun] * (PQ3-200)
    if <= 100 meters, full distance * 100meter price
    else if <= 200, 100 * 100meter price plus (full distance - 100) *200meter price
    #### mine cuts of here and assumes that anything higher is charged at a sigle rate but you can continue the same routine indefinatly####
    else, 100 * 100meter price + 100 * 200meter price + (full distance*lastPrice

    now the bit you probabliy missed 135 is more that 100 to misses the first if and is caught by the second so using your values
    135 = 100*75+35*82

    which is exactly what you asked for

    Originally posted by playernovis
    Guys,

    Do you really read the task? You cannot calculate it your way! IT'S WRONG!

    135m is
    100 * 75 + 35 * 82

    NOT 135 * 82

    look at the function I sent yesterday!

    Jiri
    Last edited by m.timoney; 12-02-02 at 13:50.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    in VB the best way to do it would be

    Code:
    'untested
    dim rates(#how ever many you need#)
    sub pop()
       for i = 0 to Ubound(rates)
          #fill in the values from table or were ever#
       next
    end sub
    
    function calc(distance as integer, ratepointer and integer)as integer
       if distance <= 100 then calc = rates(ratepointer)*distance _
       else calc = 100 * rates(ratepointer) + calc(distance - 100,ratepointer+1)
    end function
    
    function getQuote(distance as integer)as integer
       call pop
       getQuote = calc(distance,0)
    end function
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8

    Update on problem

    Hi folks

    First up thank you all for your replies to my post, I appreciate everyones help here.

    Now, I'm no programmer and I realise you guys know your stuff so any problems I have so far encountered have no doubt been due to my own stupidity or ignorance. I have tried the suggestions that have been given and for the most part they don'y work because either I don't know how to implement them correctly into my DB or I have failed to supply enough information on the problem.

    So, I have re-done a test DB with several records included as well as my own manual calculations on what the actual total costs per hole should be. I have done my best to include as much info as I can so that any possible solutions can be tested against these manual calculations to see if the code/queries/solutions will work.

    I guess now you can see things better and understand what the whole problem is. I apologise for any confusion and frustrations I have caused by my ignorance. As you can see I don't really know the code languages so if you think you have a solution could you please be patient with me....I will get it eventually....beat a dog enough and he will get the hint! 8o)

    Thank you again for all your help, whatever happens I am learning more with each post.

    Here's the new DB
    Attached Files Attached Files

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any chance you could save the mdb in access 97 format? otherwise i can't read it


    rudy

  15. #15
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8
    Originally posted by r937
    any chance you could save the mdb in access 97 format? otherwise i can't read it


    rudy

    Rudy
    I tried to convert it but it comes up with "invalid field data type"

    Any suggestions?

Posting Permissions

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