| | |
|
|
|
Calculated field with incremental costs |
|---|
| message from =?Utf-8?B?R2Vyb24=?= on 27 May 2004 |
Hi,
FrmProductCosts
Product 1-10 boxes - 11-20 boxes- 21-30 boxes- Minimum Order Value
A $2.50 $2.25 $2.00 $20.00
B etc
FrmInvoiceCharge
Cust Product boxes Cost
In the cost field I would like to calculate that if the cust orders 7 boxes of Product A the minumum $20.00 kicks in. Also how do I phrase the expr to look up the amount of boxes to return the cost per box?
Thanks
Geron
|
| Allen Browne replied to =?Utf-8?B?R2Vyb24=?= on 28 May 2004 |
ProductID foreign key to Product.ProductID
Qty Number minimum
CostEach Currency
Enter records like this:
A 1 $2.50
A 11 $2.25
A 21 $2.00
B 1 $9.00
B 11 $9.00
B ...
You can then lookup the price in your order form with something like this:
DLookup("CostEach", "tblProductCost",
"(ProductID = " & Nz([ProductID], 0) & ") AND (Qty >= " & Nz([Qty], 0) &
")")
Since the Minimum Order Value seems to be related to the product, place that
in the Product table.
More help on DLookup():
http://allenbrowne.com/casu-07.html
|
|
Archived message: Calculated field with incremental costs (Microsoft Access)
