[AccessD] Something too hard for me...

bruce_bruen at mlc.com.au bruce_bruen at mlc.com.au
Sun Feb 8 17:13:39 CST 2004





A bit late on the input but nethertheless...

Kostas,
Without understanding anything about the business rules of your situation,
I just wanted to add the folllowing.
I have seen this problem many times in distribution and sales systems.  The
best general solution I have seen is the use of a "margin" column in the
structure table, viz
|--+----+--------+----------+---------|
|ID|Base| Limit  | BaseCost | Margin  |
|--+----+--------+----------+---------|
|  |    |        |          |         |
|--+----+--------+----------+---------|
| 1|   0|       9|     $0.00|$6,000.00|
|--+----+--------+----------+---------|
| 1|  10|      19|$55,000.00|$3,960.00|
|--+----+--------+----------+---------|
| 1|  20|      29|$72,000.00|$3,593.33|
|--+----+--------+----------+---------|
| 1|  30|99999999|$98,000.00|$3,500.00|
|--+----+--------+----------+---------|
| 2|   0|       9|     $0.00|$5,000.00|
|--+----+--------+----------+---------|
| 2|  10|      19|$47,000.00|$3,300.00|
|--+----+--------+----------+---------|
| 2|  20|      29|$60,000.00|$2,640.00|
|--+----+--------+----------+---------|
| 2|  30|99999999|$72,000.00|$2,500.00|
|--+----+--------+----------+---------|
| 3|   0|       9|     $0.00|$7,000.00|
|--+----+--------+----------+---------|
| 3|  10|      19|$65,000.00|$4,235.00|
|--+----+--------+----------+---------|
| 3|  20|      29|$77,000.00|$3,373.33|
|--+----+--------+----------+---------|
| 3|  30|99999999|$92,000.00|$3,300.00|
|--+----+--------+----------+---------|



This structure allows the marketers to fine tune the marginal costs to make
higher volume sales more attractive, can be maintained by the marketers
rather than having to change the margin cost calculation code everytime
they want to push a particular bracket, and allows "simpler" querying of
the table to get the correct pricing.

SELECT tblCost.ID, tblCost.Base, tblCost.Limit, tblCost.BaseCost,
tblCost.Margin, (([Enter Q]-[Base])*[Margin])+[BaseCost] AS TotalCost
FROM tblCost
WHERE (((tblCost.ID)=[Enter station ID]) AND ((tblCost.Base)<[Enter Q]) AND
((tblCost.Limit)>=[Enter Q]));

Just thought this might be of interest.
Bruce



<Snip>






More information about the AccessD mailing list