Calculation for tiered discounting

[Guest]

Hi there,

I have a bit of a problem. In MS Excel, I can easily compute a tiered
discount using the mininum and maximum functions. In Access however, this
doesn't work.

Basically, what i want to do is this:

Create three fields in my query labeled, TierOne, TierTwo and TierThree, for
which:

Tier One Discount:

If the 'Volume Impression Charge' is 120 to 130% of the 'Target Volume
Amount', then discount this portion 8%, else discount is $0.00

Tier Two Discount:
If the 'Volume Impression Charge' is 130 to 140% of the 'Target Volume
Amount', then discount this portion 10%, else discount is $0.00


Tier THree Discount:
If the 'Volume Impression Charge' is greater then 140% of the 'Target Volume
Amount', then discount this portion 12%, else discount is $0.00


Then I add these values up and subtract it from the 'Basic Charge' (which is
the Impressions Printed * Cost Per Impression

This is the total cost.

Can anyone help me with these calculations?

 

[Michel Walsh]

Hi,


Use a temporary table that holds the discount:


Discounts ' table name
Volume, Discount 'fields name


DMIN( "Discount" , "Discounts" , ActualVolume & " >= Volume )



Have a record with Volume = 0 and Discount=0.


Since the "constants" are now in a table, not in your code, that makes it
easy to "customize" the data, and to modify it, without having to expose
your code. That is one of the various power of a "database", even if it
involves just a small table.

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


A cumulative discount "by layers". If you use a special table organization,
that becomes really easy. So, to be sure I understand, here an example


from 0 to 120, discount = 0%
from 120 to 130, discount = 10% on what exceed 120
from 130 to 140, discount = 12% on what exceed 130
from 140 to 9999, discount = 11% on what exceed 140 ( just for
illustration, I use 11% rather than your 12%, it will be more "illustrative"
that way )


so, some numerical examples:
if x = 127, we get 10% on 7 ==> .10*7 = 0.7
if x = 132, 10% on 10 + 12 % on 2 ==> .10*10 + .12*2 = 1.24
if x= 155, 10% on 10 + 12% on 10 + 11% on 15 ==> 3.85



The table I propose can look like:

Discounts ' table name
LowLimit, CumulDiscount ' fields name
0 0
120 .10 ' cumul =.1
130 .02 ' cumul = .12
140 -.01 ' cumul = .11


then the query is:

==================
SELECT SUM( (x-LowLimit) * cumulDiscount ) As TotalDiscount
FROM myTable INNER JOIN discounts
ON myTable.x >= discounts.LowLimit
GROUP BY accountID
==================

indeed, if x= 132, the SUM will do:


(132-0)*0 + (132-120)*.1 + (132-130)*.02 = 1.24


as it should be.


( I assume the working table, myTable, has two fields, accountID and x, the
value we compare to get the discount).


The trick is to build the special table Discounts. The CumulDiscount value
must be such that summing all the values up to that point gives the real
discount. As example, with

Discounts ' table name
LowLimit, CumulDiscount ' fields name
0 0
120 .10
130 .02



since, for 140 + , I want a discount of 11%, having, up to now, 12%, the
value to store is thus -1% :


140 -0.01



Hoping it may help,
Vanderghast, Access MVP