Help with setting up price bands

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

Anybody got advise on how I can set up price bands.
Let me explain: in an order entry form a quantity of 1 is entered for a
product, the price each will then display the price given for that product.
If the quantity happened to be 10 the price each would be different to
reflect the greater amount sold. If the quantity happened to be 50 the price
each would again be different to reflect the greater amount sold.
How best should I approach this one.

Thanks if you can be of any help.
Pat
 
Pat,

If I understand correctly what you are trying to do:
You have a scaled quantity discount and want to bring up
the net unit price, depending on the quantity? If that's
the case, you can do it quite nicely with a bit of VB
code. First of all, you need a table with the quantity
breakpoints and the corresponding discount percentage for
each one, e.g:
Breakpoint Discount
5 0% 'e.g. up to 5 units no dicount
30 4% 'e.g. 6-29 units -> 4%
60 7% 'e.g. 30-59 units -> 7%
99999 10% 'e.g. 60+ units -> 10%

(last qty breakpoint is an absurdly big number that will
never occur in an actual order).
Then you need a piece of code that opens this table as a
recordset descending on breakpoint, browses through the
records until the order quantity is greater that the
breakpoint,picks up the discount percentage from that
record, calculates the discounted unit price and puts it
in the form field (test around breakpoints to make sure
they behave as you intended them).
The On Change event of the qty field on your form is a
very good trigger for this code (so it automatically
updates the price every time you change the qty).

HTH,
Nikos
 
That's not quite what I am looking to do, but a good attempt non the less.
I have a form called Products, in addition to product name etc there are
controls where the price each can be entered. Example follows:

Product: Apples
PriceEach 1-9: 0.30
PriceEach 10-49: 0.25
PriceEach 50-99: 0.23
etc..

The PriceEach is entered into each of the controls, this means a quantity
discount is not percentage based.

Pat
 
Good Nikos
I thought a separate table would be needed.
Constructing the table and query I should be able accomplish,
constructing code on the otherhand will be more challenging.
If it is possible and if it is not too much trouble for you could
you be so kind and provide sample code on how it would work?
Then I could replace parts of the code where necessary.

Regards
Pat
 
Pat,

OK, got you...
(Assumption: breakpoints are the same for all products)
1.Open the table as a recordset and find the record for
the particular product
2.Use nested IF's on the quantity to identify the band
3.Retrieve the value of the field corresponding to the band

On the other hand: what I proposed is still workable with
a change in your db structure, i.e. keep the prices in a
separate table, like I decsribed the discounts table
yesterday; you will need to add a field for product code.
The rest of the logic is the same, except instead of
opening the whole table as a recordset, you should use a
query to filter on the particular product. This approach
has some additional advantages over yours: (a)it allows
you to set up as many bands per product as you wish, (b)
break points can vary by product, and (c) you don't end up
with a very sparse table in case some products have more
bands than most others.

HTH,
Nikos
 
Pat,

The code should be fired by the on change event of your
quantity field, and should look something like this:

Sub get_unit_price()
Dim db As DAO.Database
Dim rst As DAO.Recordset

prod = Forms("FormName").Controls("Product_ID")
qty = Forms("FormName").Controls("Quantity")
strSQL = "SELECT * FROM tblPrices WHERE Prod_ID = '" & prod
strSQL = strSQL & "' ORDER BY BreakPoint DESC"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
prc = rst.Fields("BandUnitPrice")
Do
If qty > rst.Fields("BreakPoint") Then Exit Do
rst.MoveNext
If rst.EOF Then Exit Do
prc = rst.Fields("BandUnitPrice")
Loop
rst.Close
Forms("FormName").Controls("UnitPrice") = prc
End Sub

Notes:
1.You'll need to add the DAO 3.6 reference (in the VB
editor, menu Tools > References).
2.The SQL string above assumes your product_ID field is
type text. If it is number, you'll need to remove the
single quotes insede the double ones on either side of the
prod variable.
3.Not having the database I have not tested the code, but
shouldn't be far off.

Good luck,
Nikos
 
Back
Top