Help with creating a calculated field

[Joe Robertson]

I am writing a Dabase where an employee can type in a part number and hit OK
and it returns the information on the part (E.G. cost, list price, retail
price, units on hand, description, etc.) I have all of that working, but I
can't figure out how to make the retail portion work. Retail price is the
markup on the suggested list price by the manufacturers. Example: the part
Lists for $2.00 and I want to add 20% to that to get $2.20 in the Retail box
on the form. The kicker is that depending on the List price, we get a
different mark up. For parts from .01 to $5, we get 20%; $5.01 to $10, 15%;
$10.01 to $15, 10%; $15 and up, 5%. I have a SETUP table that contains the
numbers just mentioned in their own fields, HI_Limit1, HI_Limit2, MarkUp1,
MarkUp2, etc. With all of that said this is what I need to Happen: DB looks
at the List price and compares it too all the values and then outputs the
correct calculation to the Retail Price control on the form.

I am using Access 2002.
Thanks for your time and knowldege

Joe

 

[Wayne-I-M]

Hi Joe

If these are the "only" possible marings
Between .01 and 5 margin = 20%
Between 5.01 and 10 margin = 15%;
Between 10.01 and15 margin = 10%
Above15 margin = 5%.

Then the simplest method would be a nested IIF in the query

I would add a date field to your setup table as the rates may change in the
future. This way if the rates did change you would not need to rewrite the
IIF or change any of the past records.

 

[Wayne-I-M]

Hi Joe

I have just created a 2 table DB with your fields (it's sunday morning and
it's either this or clean the car)

An other method I found to work simply was to have a MarginID in the setup
table and link this to the PartNumber table.

I created a small combo on a form based on the SetUp table and this would
allow the user to select the appropriate margin for any product. Of course
you could automate this by using a Where clause in the combo source (but have
been "instructed" to go and clean the car now, so didn't get time for this ?).

This siad I still found the nested IIF in the query to be simpler for a user
- the less people have to do the less likely things are to go wrong ?

Good uck

 

[Larry Daugherty]

Hi Joe,

Given what you/ve posted, I'd use a lookup table; tblPriceBreak with
each price break and its associated markup in the table.
tblPriceBreak might have fields PriceBreakID, Autonumber; ListPrice,
currency; PercentMarkkup, number. By creating a form on that table
you'll be able to easily maintain changing price breaks and markups
over time.

In the AfterUpdate Event of the textbox or combobox you use to enter
the item number call a procedure (that you will write) and pass it the
item's List Price. It will find the appropriate price break (the 1st
Price Break that is equal or greater than the item's price) and return
the Marked Up Price.

HTH

 

[Ken Sheridan]

Joe:

I take it this is simply to enable staff to look up the current price data,
and not an invoice record on a bound form. If it’s the latter you should not
use unbound computed controls but bound controls and push the data in with
code. Computed controls will always reflect the current price data whereas
with an invoice record you want the price to remain static as that when the
invoice was created, regardless of subsequent price changes.

With that caveat change your SetUp table along the lines suggested by Larry
e.g. with two columns BasePrice (the price at which each mark-up range
starts) of currency data type and a MarkUp column of single precision number
data type. So the table would look like this:

BasePrice MarkUp
0.01 0.2
5.01 0.15
10.01 0.1
15.01 0.05

Add the following function to the form's Module:

Private Function GetRetailPrice()

Dim curListPrice as Currency

' look up list price of part from Parts table
curListPrice = _
Dlookup("ListPrice", "Parts", "PartNumber = " & _
Me.txtPartNumber)

' look up highest mark-up for parts of or above the list price
' and add to list price
GetRetailprice = _
curListprice * (1 + _
DMax("Markup", "SetUp", "BasePrice >= " & curListPrice))

End Function

Where txtPartNumber is the control on the form in which the part number is
entered. I've assumed PartNumber is a number data type. If its text data
type amend the code:

curListPrice = _
Dlookup("ListPrice", "Parts", "PartNumber = """ & _
Me.txtPartNumber & """")

The computed control for the retail price would have a ControlSource
property of:

=GetRetailPrice()

In the case of an invoice record where RetailPrice is a bound control you'd
push the value into the control using the same function with the following in
the txtPartNumber control's AfterUpdate event procedure:

Me.RetailPrice = GetRetailPrice()

Note that I've used object names without spaces above. If any include
spaces remember that they need to be enclosed in brackets, e.g. [Part
Number], [List Price] etc.

Ken Sheridan
Stafford, England

 

[Joe Robertson]

Thanks for the help! The nested IF was the easiest way to go, as the markup
will not ever change.