Creating variables to calculate (1 Viewer)

[james7705]

Good day all

I'm really hoping someone can assist me.

I'm trying to create a calculator using different sets of tariffs.

Basically what happens is i will enter an amount of units consumed.
What the calculator needs to do at the push of a button is calculate the amount owed by breaking the number down and multiplying it by the different tariffs.

e.g
tariff1 = 11.917550 (1st 6units)
tariff2 = 12.151250 (next 4units)
tariff3 = 16.876510 (next 10units)
tariff4 = 21.876960 (next 10units)
tariff5 = 27.454360 (all the units remaining after 30)

if my number is 31units

then the number is broken down as follows:
6units on tariff1
4units on tariff2
10units on tariff3
10units on tariff5
1unit on tariff5 (30+ units will be on tariff5)

i wrote some code on Brackets using JQuery and it works fine, but i don't know how to convert that to VBA:banghead:
Find attached code in .txt
View attachment 65706

 

[Ranman256]

If make a table with the tariffs and their limits,

Tariff, fee, StartUnits, EndUnits,note
tariff1 , 11.917550 ,1 ,6 ,(1st 6units)
tariff2 , 12.151250,7,10, (next 4units)
tariff3 , 16.876510 , 11,20, (next 10units)
tariff4 , 21.876960 , 21, 30, (next 10units)
tariff5 , 27.454360 ,31, null, (all the units remaining after 30)

So either with queries ,where you'd have 5 queries, 1 for each range,
Q1= where count(field) between StartUnits and EndUnits and tariff='tariff1'

Q1 processes all records for t1
Q2 would run for T2 range , etc

The alternate way would be to use vb to go record by record applying the tariff value.

 

[james7705]

Thanks Ranman256

Have you looked at the text file i attached?

I used an IF and ELSE statement with the tariffs as variables.

Is there a way i can do that with VBA as it seems a lot simpler?

I don't know VBA at all so not sure how to assign the variables and input that in the IF statement

 

[james7705]

I have attached the database that im working on to this post.

When you open it...it asks for a meter reading and a date.
When you click calculate it will open the second form where all the info is being calculated.
You will notice there are 5 empty blocks (tariff1 to tariff5) where the calculations must be displayed in.
So once the calculation has been done e.g. tariff1 has subtracted 6 from "consumption" and multiplied that 6 to the tariff1 rate then it must be displayed in the Tariff 1 block.
And so forth for each tariff.

My sincere request is that someone could assist me with this. Please.

Thank you View attachment Calculator - Copy.accdb

 

[Ranman256]

i cannot download.
tho queries are simple.

 

[The_Doc_Man]

There are ways to make this happen using the same technique that the USA Infernal Revenue Service uses for tax computations on your adjusted gross income.

Make a table that looks like THIS.

Tariff      fee               Base            StartUnits     EndUnits        note
tariff1     11.917550     0.0              1                 6                  (1st 6units)
tariff2     12.151250     71.5053        7                 10                (next 4units)
tariff3     16.876510     120.1103      11                20               (next 10units)
tariff4     21.876960     288.8754      21                30               (next 10units)
tariff5     27.454360     507.645        31                2000000000  (all the units remaining after 30)

The "Base" is just the amount added by the previous incremental tariff. So the first base is 0 'cause there is no previous increment. The second base is 6*tariff1. The third base is 4*tariff2 + 6*tariff1 etc.etc.

Now the SQL might be similar to:

stSQL = "SELECT Units, ( Base + ( ( Units - StartUnits +1 ) * Fee ) ) As TotFee " & _
           "FROM {source of units}, FeeTable " & _
           "WHERE Units BETWEEN FeeTable.StartUnits AND FeeTable.EndUnits ;"

Add other fields to this as needed from their various sources. This is a JOIN of sorts but not "really" a join. It is a constrained JOIN using BETWEEN but you can't use explicit JOIN syntax with BETWEEN (or at least I've not been able to do so) - so instead you use a filtered Cartesian join. As long as the number of records in the source of your unit data isn't astronomical, this won't run badly because the Fee table is short.

The "+1" in the table is because your numbers are 1-based. Obvious, if you had 0 units, the tariff would be 0. You might want to adjust the ranges to allow that option or at least pre-filter the units table for anomalous entries. Otherwise, this or something close to it might do what you want.

And by the way, that is NOT a typo in my opening comments about the IRS. It's just an opinion.