Price Dependant on Quantity

[jagstirling]

If I have a table of customers and the prices they are charged for
product depending on the quantity they purchase (such as);

0-10 Items $1.00
11-20 Items $1.25
21-30 Items $1.35

How can I set up my databse to ensure the correct price is selected VI
A QUERY when a certain qunatity is entered IN ANOTHER TABLE ?

In essence, I am trying to set a price within a given quantity band
ie. if the quantity was 9 then the price $1.00 would be selected, th
quantity 23 then the price $1.35 would be selected.

PLEASE CAN SOMEONE GIVE ME SOME GUIDANCE

 

[Albert D. Kallal]

Lets assume you have a typical order form.

Do you need to sum up each line..and then get a price..or is this price
change for each details?

ie:

Produuct Quantity UnitPrice Total
Oranges 15 $1.25 $18.75
Apples 5 $1.00 $5
Grapes 25 $1.35 $33.75

Or, do you want the following:

Produuct Quantity UnitPrice Total
Oranges 15
Apples 5
Grapes 25
=======
45 $1.35 $60.75


So, it is not all clear which of the above you are looking at?
(do you sum each items...for a grand total..and then figure out the rate..or
do you set your rate for EACH qty entered in the invoice/order form?

Do you have a form..where you enter in the order data as above?

Depending on which of the above approaches ...will make my answer
different..

So, which of the two scenarios do you have?

Also, you fail to mention what happens when you have a qty of 35 ??????

I will assume that you qty is always in your ranges..if not, then you need
to modify the following code...

Anyway..the code to lookup the price could look somthing like:

(warning...air code follows)

dim rstRec as dao.RecordSet
dim strSql as string
dim curRate as currency
dim intQty as integer

intQty = nz(me.Quantity,0)

strSql = "select Top 1 QRate from tblRates where QQty >= " & intQty & _
"order by QQty"
set rstRec = currentdb.OpenRecordSet(strSql)
curRate = rstRec!QRate
rstRec.Close
set rstRec = nothing


me.UnitPrice = curRate

Of course..that rates table will look like:

tblRates:

QQty Qrate
10 $1.00
20 $1.25
30 $1.35

So, you just build a query that finds the first QQty >= whatever the user
typed in.

If the user types in 5,..then QQty of 10 is >=..and the sql above returns
the $1.00 rate

If the user types in 15, then the 2nd record if the first record where the
QQty is >=...so we
grat that rate...