Solved Increase price by increased consumption (1 Viewer)

[Falcon88]

Dear all

I have a field " LtrsConsumed" on
InvoiceTBL contains consumed water liters No. .....
Where consumption price in groups as:

1 to 200 liter * 1.5 Rials
201 to 500 liter * 2.0 Rials
501 to 1000 liter * 2.5 Rials
1001 to 2000 liter * 3.0 Rials
More than 2000 liter * 3.5 Rials


Stored in GroupPriceTBL.



Exampe :
Customer 1 has consumed 1200 liters
The Total consumption value :
(200*1.5) +
(300*2) +
(500*2.5) +
(200*3)
=
2750 Rials


How to calculates this in a query by using (iif) fun. to distribute ( 1200) consumed liters , on the prices in the GroupPriceTBL ?

 

[June7]

Example: x represents the field

1.5 * IIf(x<=200, x, 200) + 2 * IIf(x>200, IIf(x>=500, 300, x-200), 0) + 2.5 * IIf(x>500, IIf(x>=1000, 500, x-500), 0) + 3 * IIf(x>1000, IIf(x>2000, 1000, x-1000), 0) + 3.5 * IIf(x>2000, x-2000, 0)


The full expression using field name might exceed character limit. Build a custom function.

 

[arnelgp]

the table structure of your GroupPriceTBL should be:

FieldName:

MinLiter (integer)
MaxLiter (integer)
Multiplier (double)

MinLiter            MaxLiter         Multiplier
1                   200              1.5
201                 500              2
501                 1000             2.5
1001                2000             3
2001                9999             3.5

my approach is vba (unless some wiseguy reacts).
put in a module:

public function fnGasConsume(liter as integer) as double
dim rs as dao.recordset
dim runTotal as double, dim tmp as double
set rs=currentdb.openrecordset("GroupPriceTBL")
rs.MoveFirst
while liter > 0
	tmp = rs!MaxLiter
	if liter <= tmp then tmp = liter
	runTotal = runTotal + (tmp * rs!Multiplier)
	liter = liter - tmp
	rs.MoveNext
       If rs.eof then rs.moveprevious
Wend
rs.Close
set rs=Nothing
fnGasConsume = runTotal
End Function

on your Query:

select LtrsConsumed, fnGasConsume(LtrsConsumed) As TotalConsumption From InvoiceTBL;

 

[Mark_]

Slightly easier approach than arnel's

Remove Min, but add "Base" Your Base" is the amount of all previous groups.
You then look for the highest MaxLiter less than your [Consumed].
Your calculation is then (([Consumed] - MaxLiter) * Rate ) + Base all from ONE record. This also handles when you have a "Highest" tier.

To get the highest is basically "Select Top 1 From Tbl Where Tlb.MaxLiter < [Consumed]"

 

[Falcon88]

the table structure of your GroupPriceTBL should be:

FieldName:

MinLiter (integer)
MaxLiter (integer)
Multiplier (double)

MinLiter            MaxLiter         Multiplier
1                   200              1.5
201                 500              2
501                 1000             2.5
1001                2000             3
2001                9999             3.5

my approach is vba (unless some wiseguy reacts).
put in a module:

public function fnGasConsume(liter as integer) as double
dim rs as dao.recordset
dim runTotal as double, dim tmp as double
set rs=currentdb.openrecordset("GroupPriceTBL")
rs.MoveFirst
while liter > 0
	tmp = rs!MaxLiter
	if liter <= tmp then tmp = liter
	runTotal = runTotal + (tmp * rs!Multiplier)
	liter = liter - tmp
	rs.MoveNext
       If rs.eof then rs.moveprevious
Wend
rs.Close
set rs=Nothing
fnGasConsume = runTotal
End Function

on your Query:

select LtrsConsumed, fnGasConsume(LtrsConsumed) As TotalConsumption From InvoiceTBL;

Very thanks

After trying the function , it works good for the first two prices only. but after that it gives wrong results. I give LtrsConsumed values like :
200 , it's returns 300 , RIGHT

500 it's returns 1050 , RIGHT

1000 it's returns 2600 , WRONG ( THE RIGHT IS 2800)

2000 it's returns 6250 , WRONG ( THE RIGHT IS 6800)

4678 it's returns 17451 , WRONG ( THE RIGHT IS 18851)
Note: i uses this prices:

Code:

MinLiter MaxLiter Multiplier
1 200 1.5
201 500 2.5
501 1000 3.5
1001 2000 4.0
2001 9999 4.5

Note : "LtrsConsumed" a long number data type.

1.Where is the problem in the code ?


2. Let's suppose we need to treats large Numbers like "hundreds of thousands" in the MinLiter and MaxLiter fields, what is the best data type to choose ?

 

[arnelgp]

here is the new code, i've check it with your last post
and it is now returning the correct value:

' ok i change the structure
'
' MinLiter (long)
' MaxLiter (long)
' Multiplier (double)
'
' MinLiter   MaxLiter    Multiplier
' ==================================
' 1          200         1.5
' 201        500         2.5
' 501        1000        3.5
' 1001       2000        4
' 2001       100000000   4.5
'
' for your first example
' 1200 -> 2750
'
Public Function fnGasConsume(liter As Variant) As Double
    Dim rs As DAO.Recordset
    Dim runTotal As Double, tmp As Double
    
    liter = Nz(liter, 0)
    runTotal = 0
    If liter < -1 Then Exit Function
    Set rs = CurrentDb.OpenRecordset("GroupPriceTBL", dbOpenSnapshot)
    With rs
        .MoveFirst
        While liter > 0
            tmp = !Maxliter - !Minliter + 1
            If tmp > liter Then tmp = liter
            runTotal = runTotal + (tmp * !Multiplier)
            liter = liter - tmp
            .MoveNext
            If .EOF Then .MovePrevious
        Wend
        .Close
    End With
    Set rs = Nothing
    fnGasConsume = runTotal
            
End Function

 

[Falcon88]

here is the new code, i've check it with your last post
and it is now returning the correct value:

' ok i change the structure
'
' MinLiter (long)
' MaxLiter (long)
' Multiplier (double)
'
' MinLiter   MaxLiter    Multiplier
' ==================================
' 1          200         1.5
' 201        500         2.5
' 501        1000        3.5
' 1001       2000        4
' 2001       100000000   4.5
'
' for your first example
' 1200 -> 2750
'
Public Function fnGasConsume(liter As Variant) As Double
    Dim rs As DAO.Recordset
    Dim runTotal As Double, tmp As Double
    
    liter = Nz(liter, 0)
    runTotal = 0
    If liter < -1 Then Exit Function
    Set rs = CurrentDb.OpenRecordset("GroupPriceTBL", dbOpenSnapshot)
    With rs
        .MoveFirst
        While liter > 0
            tmp = !Maxliter - !Minliter + 1
            If tmp > liter Then tmp = liter
            runTotal = runTotal + (tmp * !Multiplier)
            liter = liter - tmp
            .MoveNext
            If .EOF Then .MovePrevious
        Wend
        .Close
    End With
    Set rs = Nothing
    fnGasConsume = runTotal
            
End Function

Very thanks
It works good.
It solved

thanks ..for mr. Mark_ , post #4 , please re - explain step by step .

 

[Mark_]

Please take a look at the attached.

It is one hit against one record that includes a base amount (calculated from all previous maximums) so it allows for "excess charge" to be built in to your total.

This is very useful when you have a charge for "Over limit" or other amounts that you use all of the time that become a real headache to rework in code. In many businesses this is a helpful approach as you could add a "Customer ID" to the table and have different rates for different customers (or districts, industries, what have you) without having to write separate functions to deal with them.