| | |
|
|
|
Re: Calling ALL Access gurus ... "Come and have a go if you think your smart enough" ! |
|---|
| message from Nikos Yannacopoulos on 7 May 2004 |
A basic scenario (just for calculating the pricing) would involve at least
four tables:
CUSTOMERS
CustID
Name
Address (possibly in several fields)
PrLstID
PRODUCTS
PrdID
PrdDescription
PRICELISTS
PrLstID
PrdID
MinQty
UnitPrice
PROMOS
PrmID
PrmDecsription
ValidFrom
ValidTo
DiscntRate
Tables are joined on fields with the same name. This design allows for each
product having a different number of price bands, with different boundaries.
To get the price for a given order quantity you would use a query that
returns the records from PRICELISTS where MinQty is <= order quantity, and
select the record where MinQty is max, or, better yet, do it in code. To
apply promos you could use a query filtering on dates if only one promo may
be applicable at any given date, or resort to code if there may be several
(so as to apply them in sequence). The underlying assumption is that promos
are global for all customers, all products, otherwise you would need
additional tables for customers/products a promo applies to.
If you want to keep a record of sales made (a very sensible thing to do),
you would need two additional tables, one for sale header, and one for sale
items:
HEADER
SaleID
CustID
SaleDate
ITEMS
SaleID
PrdID
Qty
Price
The Price field is optional; in theory it can be recalculated whenever
required in much the same way as for calculating the sale prices, but in
practice it may be argued that storing it at the time of first calculation
adds very little to minimal to the total db size, and makes retrieval of
statistics a lot easier and faster. As a matter of fact, that is what all
the major commercial ERP's do (actually they go further, storing base unit
price, discount rate, net price and tax).
HTH,
Nikos
"jagstirling" <jagstirling.15roa1@nospam.MSAccessForum.com> wrote in message
news:jagstirling.15roa1@nospam.MSAccessForum.com...
|
|
Archived message: Re: Calling ALL Access gurus ... "Come and have a go if you think your smart enough" ! (MS Access Error Message)
