Calculate sales discounts

[Guest]

I need to calculate the daily discounts given by salesmen on the products
they sell. I have an order details table with item #, quantity sold, price
per unit(actual price charged by salesman),total and date.

I have an inventory table with prices in 5 columns p1,p2,p3,p4 and p5. The
price that is supposed to be used is determined by the quantity sold. Each
price field has an associated quantity range stored in to separate fields.
P1LL, and P1UL. So say for pricing level 1 on a particular product a
quantity of 1-5 will qualify the customer for level one pricing.

I need a query that can calculate the difference between what the salesman
should have charged and what they actually charged, taking into account the
quantity discounts that we have listed in our inventory table.

 

[Michel Walsh]

Hi,




Vertical.


If you ever have a sixth range, you will need a sixth columns, horizontally,
and for the range, and for the value of the discount. But if you are
vertical, no new fields will be required, just new record. Since the field
names won't change, your query would not change either....

WIth:

Lower Upper DiscontRate ' assumed fields name
1 5 0%
6 10 5%
11 25 15%
26 9999 25% ' data sample




Having the amount sold in tableName.QtySold, you can then get the
discount rate:


SELECT AmountSold,
DiscontRate,
AmountWithoutDiscount * DiscontRate,
...
FROM tableOfSales , tableOfDiscounts
WHERE tableOfSales.QtySold >= tableOfDisconts.lower
AND tableOfSales.Qty.Sold <= tableOfDisconts.upper



you can add calculated fields in the SELECT clause (I represent that
possibilities by the ... which are not really part of the syntax, is it
needed to say )


Hoping it may help,
Vanderghast, Access MVP