Calling ALL Access gurus ... "Come and have a go if you think your smart enough" !

[jagstirling]

I have a general question based around the fictitous scenario below;
**********************************************************************

I am a confectioner selling candy and wishing to hold details of m
customers in a table.

One of the fields held is the price each customer is charged for eac
type/brand of candy.

The price each customer is charged for my candy is different DEPENDAN
on three factors;

* The quantity that they buy (split into bands as below).
0-10 Items $1.00
11-20 Items $1.25
21-30 Items $1.35


* The type/brand of candy that they buy.

* General percentage discounts provided on brands on an ad hoc basis.

How can I set up my database to ensure that the right price is charged

DEPENDANT ON THE AMOUNT OF CANDY PURCHSED ?
DEPENDANT ON THE TYPE/BRAND OF CANDY PURCHSED ?
DEPENDANT ON ANY AD HOC DISCOUNTS OFFERED AT THE TIME ?

For example, if (using the info above) I typed in 9 items I would wan
the price $1.00, 23 items would return a price of $1.35 etc etc etc.

In reality my quantity bands are broad so entering individual price
for each quantity is not an option.

CAN ANYONE HELP PLEASE

 

[Nikos Yannacopoulos]

It's more a question of "I've been there" rather than "I'm smart enough"...
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

 

[Tony Toews]

I have a general question based around the fictitous scenario below;

Sounds like a homework question to me.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Nikos Yannacopoulos]

It's more a question of "I've been there" rather than "I'm smart enough"...
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