Table lookup

[John Topley]

How do I do the following:

I want a Discount table which contains a quantity (or quantity range)
and associated discount. For example -

Qty Discount
3-5 2%
6-10 5%
11-20 7%

etc

When an order is entered I want to obtain the appropriate discount %
and apply it to the price of the ordered product (and/or add to an
Order Detail record). Assume there is an Order Table, Product Table
and Order Detail table; the latter is a junction table relating Orders
and Products.

So how do (can) I select the appropriate row in the Discount table
based on the product order quantity?

Thanks in advance.

John

 

[Jeff Boyce]

John

One approach to this is to build your QuantityDiscount table with a minimum
of two fields. A MinimumQuantity and a Discount. You could use a query to
find largest MinimumQuantity that is less than the QuantityOrdered (I think
I said that correctly...). So, in your example a quantity of 7 is greater
than the low end of your 3-5 and your 6-10 rows, but the largest of THOSE is
the 6-10 row.

 

[Tim Ferguson]

(e-mail address removed) (John Topley) wrote in

Qty Discount
3-5 2%
6-10 5%
11-20 7%


So how do (can) I select the appropriate row in the Discount table
based on the product order quantity?

First of all, make the Qty column a bit more friendly: use a Numeric column
and keep the lower bound in it. The first row helps to avoid NULL errors.

Qty Discount
0 0
3 2
6 5
11 7
21 ?


Then you can get the discount with a TOP query (not tested, but it should
be pretty close).

SELECT TOP 1
Discount
FROM Discounts
WHERE Qty <= [Quantity Ordered]
ORDER BY Qty ASC;


Hope that helps


Tim F

 

[John Topley]

(e-mail address removed) (John Topley) wrote in

Qty Discount
3-5 2%
6-10 5%
11-20 7%


So how do (can) I select the appropriate row in the Discount table
based on the product order quantity?

First of all, make the Qty column a bit more friendly: use a Numeric column
and keep the lower bound in it. The first row helps to avoid NULL errors.

Qty Discount
0 0
3 2
6 5
11 7
21 ?


Then you can get the discount with a TOP query (not tested, but it should
be pretty close).

SELECT TOP 1
Discount
FROM Discounts
WHERE Qty <= [Quantity Ordered]
ORDER BY Qty ASC;


Hope that helps


Tim F

Jeff/Tim,
Many thanks for your help which has moved me along BUT I
still have a problem.

I am using the Northwind sample database to (slowly) increase my
understanding of Access. To this end, I replaced the Discount field in
the Order Detail Extended query with a field based on Tim's query with
Order By set to DESC.
This works fine for all existing Orders but when I now start a new
order I am unable to enter the product details; the data entry line
does not appear.

I assume this because the discount query cannot be executed as there
is no [existing] order detail on which to work e.g (hope this is
clear!).

So I think I need to enter an Order Detail line (as per original
Northwind) and only then calculate(add) the discount field. Am I
thinking straight on this? and if so, how do I add such a discount
field. ( I have been able to add other simple calculated fields to
the Order Detail line e.g. Total Price = {Quantity] *[Unit Price]).

Again, thanks in advance for any help.

John

 

[Tim Ferguson]

(e-mail address removed) (John Topley) wrote in

So I think I need to enter an Order Detail line (as per original
Northwind) and only then calculate(add) the discount field. Am I
thinking straight on this?

I have to confess that I have not looked closely at the Northwind sample
for quite a long time, so the details that you quoted did not make a lot of
sense to me. It seems to me, though, that until you have entered an
OrderDetail, there is no Quantity to calculate the discount on.

if so, how do I add such a discount
field. ( I have been able to add other simple calculated fields to
the Order Detail line e.g. Total Price = ([Quantity] *[Unit Price]).

Probably the simplest way is to use save the discount lookup query as above
as a proper QueryDef, but replace the parameter for a pointer to the
appropriate control on the OrderDetails subform, something like this
(again, it's not tested so treat it with caution!):-

SELECT TOP 1
Discount
FROM Discounts
WHERE Qty <=
Forms!MainOrderForm!subfmOrderLines.Form!Quantity
ORDER BY Qty DESC;

and then put in a control on the subfmOrderLines subform with its
controlsource set to

DLookUp("Discount", "qryLookUpDiscount")



Failing that, you might wrap the query up in SQL function and use that in
the control source.

Hope that helps


Tim F