pricing formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I set access to figure differant pricing depending on cost. For
example I have a price list and if the part cost me $0.01-$2.00 I'd like to
times by 4 if it cost $2.01-$5.00 I'd like to times by 3.5 and so on, please
help, I am new at this and on a dead line.--
jimmy
 
Create a table with fields:
MinBuyPrice Currency
Factor Number size Double in the lower pane.
Save the table with a name such as tblBuyFactor.

You can then lookup the factor to use like this:
DLookup("Factor", "tblBuyFactor", "MinBuyPrice >= " & [BuyPrice])

Typically you would do that in the AfterUpdate event of the Price field in
your form. You may also need to DLookup() your buy-price for the part if you
want to use the AfterUpdate even of the PartID field. There's an example of
that in the Nortwind sample database, Order Details subform, ProductID
control.

For more help with DLookup, see:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html
 
Jimmy,

Allen Browne is an Access expert and very comfortable with VBA. I am just a
shade more experienced than an out
and out newbie and would tackle this problem in an entirely different way
since I am NOT comfortable with VBA. I would
construct a query from the Partmaster table that would include the
Partnumber, Description, and Cost and build some expressions in query design
view. Here's how:
1. Open the Database window, select the Query tab and click the "New"
button.
2. Select "Simple Query Wizard and click OK.
3. In the wizard select Partmaster (or equivalent) from the list of tables.
Then choose the fields I mentioned above. Click next, rename the query if
you like, and Finish.
4. In an empty column of the grid in Design view add this expression in the
"Field" row: Price1: IIf([Cost]<=2,[Cost]*4)
(Please substitute the name of your cost field if it is different and rename
Price1 if you so choose). BTW, the colon after Price1 is important. It lets
Access know that this is the name of your new field and that what follows is
an expression. I suggest you cut and paste these expressions and then adjust
the bracketed field names.
5. In the next empty field enter this: Price2: IIf([Cost] Between 2 And
5,[Cost]*3.5)
6. Keep going like this until all your prices are covered. If you would like
all these calculated fields to display in a single column enter
this: AllPrice: CCur([Price1] & [Price2] & [Price3]) in the next open
column. (The Ccur forces the field to display as currency and may be removed
if you don't want it).
7. You can also compare your current retail price to the "formula price"
with: Expr: [RetailPrice]/[AllPrice] but you would first have to add the
RetailPrice field (or whatever your database calls it) to the grid. You
could then sort by this ratio to determine which of your current prices is
most in need of correction.

I hope you find this helpful.

Edward G
 
Back
Top