1. Registered User
Join Date
Nov 2002
Location
Australia
Posts
8

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!)

2. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

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. Registered User
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. Registered User
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....

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

Jiri

7. Registered User
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)

8. Registered User
Join Date
Nov 2002
Location
San Francisco
Posts
251

hmmm

Guys,

135m is
100 * 75 + 35 * 82

NOT 135 * 82

look at the function I sent yesterday!

Jiri

9. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820

11. Registered User
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,

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.

12. Registered User
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```

13. Registered User
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

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

rudy

15. Registered User
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
•