Can my '07 Access or Excel compare values, then calculate them?

[Russ from Apple Valley, Ca]

G'day folks, and happy Holidays.

Man I'm glad I found you! I've been reading, searching and educating myself
about my database for a few days now. And I'm making progress. Although I
have been using Office for years.

I have been experiencing a glitch for quite some time now...

I'm using Office '07 on a good fresh install and it's working very well.

What I'm trying to do;

I need my database Form, or excel if necessary, to add mark-up to my parts
cost. I'll sell parts plus a profit in my business.

I want Access to store a value for parts including a markup. That field is
now called Sell_Price. I figured when I enter a value into Part_Cost, Access
can determine the mark-up multiplier, perform the calculation, and store the
value in Sell_Price.

The problem is as the parts go up in value, or cost me more, the mark-up
must go down incrementally, likely in about 4-5 tiers or levels of mark-up.
(that way I can pass the savings to my customers so they feed me lunch more
often!)

Example;
=IF(AND(Part_Cost>.5,Part_Cost<=400),*2,
IF(AND(Part_Cost>400,Part_Cost<=1200),*1.8,
IF(Part_cost>1200,*1.5 SOMETHING MISSING RIGHT? )))

In the case above if the parts cost less than $400 then I'll double them,
from $400 to $1200 markup is only 80% and above $1200 I will add 50%. If I
can get that formula working I can modify it further.

Questions:

How do I get my database, or excel, to find the right multiplier for parts
cost I enter?

And why doesn't that above statement / formula work? (I tried it in excel
today but I think somethings missing)

When I do get this working, where do I return the value in my form? Is it
recommended to have an unbound field store the calculated value?

I'll keep searching, this has taken me a really, really long time to figure
out. But I will do it, eventually.

Thanks for your consideration,
Russ S

(e-mail address removed)

 

[Al Campagna]

Russ,
In Access...
Sell price should not be a bound field. Since you save the PartCost, it
is not necessary to also save the SellPrice. It can always be recalculated,
on the fly, in any subsequent form, query, or report.
In the ControlSource of an unbound text control on the form...

= IIF(PartCost<400, PartCost * 2, IIF(PartsCost > 400 and PartsCost <
1201, PartsCost * 1.8, PartsCost * 1.5))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Russ from Apple Valley, Ca" <Russ from Apple Valley,
(e-mail address removed)> wrote in message
news[email protected]...

 

[MikeJohnB]

Further to Al's response

In Excell, you have missed the closing argument of the last If

=IF(AND(A1>0.5,A1<=400),A1*2,IF(AND(A1>400,A1<=1200),A1*1.8,IF(A1>1200,A1*1.5,"Too Small")))

Because the case could be less than 0.5 I have substituted "Too Small"

Hope this helps

Regards

Mike B

 

[MikeJohnB]

Sorry forgot to say, that equation is placed in cell B1 when the original
price is in A1 but you could adapt it to your named range I guess or drag
down the whole of the column B and place your original Price in Column A.

Obviously, Column A must be formatted to Number

Regards

 

[a a r o n _ k e m p f]

Russ;

if you're trying to learn a database-- learn a database with a future.
Jet (Access) has been obsolete for a decade.

Meanwhile, SQL Server is the worlds most popular database.

It's time to lose the training wheels, kid!

-Aaron

 

[Tony Toews [MVP]]

Sell price should not be a bound field. Since you save the PartCost, it
is not necessary to also save the SellPrice. It can always be recalculated,
on the fly, in any subsequent form, query, or report.
In the ControlSource of an unbound text control on the form...

= IIF(PartCost<400, PartCost * 2, IIF(PartsCost > 400 and PartsCost <
1201, PartsCost * 1.8, PartsCost * 1.5))

I disagree. What happens if the formula changes next year? What if
there is a special deal worked out? Cost and selling price are the
exceptions to the normalizing rule, although they aren't really
exceptions, as they are the cost and price in effect at the time of
the sale.

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Al Campagna]

Tony,
I agree with you, but... I usually try to tailor the response to just
what the OP describes.
I simply coded the IIF statement to match his example.

And yes... I should have asked if those rates would ever change.
Thanks for the tip,
Al Campagna

 

[Russ from Apple Valley, Ca]

I disagree. What happens if the formula changes next year? What if
there is a special deal worked out? Cost and selling price are the
exceptions to the normalizing rule, although they aren't really
exceptions, as they are the cost and price in effect at the time of
the sale.

Tony

OK here's what I've tried so far in Access and so far I haven't gotten it
right...

=IF(AND([PARTCOST] >0.5,[PARTCOST]<=400),[PARTCOST]
*2,IF(AND([PARTCOST]>400,[PARTCOST]<=1200),[PARTCOST]*1.8,IF([PARTCOST]>1200,[PARTCOST]*1.5,"Too Small")))

I'm using data from a table that has about 500 records. I'm trying to get a
form to do these calculations.

I've tried putting this formula into an unbound field, and into the field
where I'd normally look for the calculated value, of course one at a time.

I've made sure to have the three tables in the database linkd by
relationships.

I did get the excel formula to work with a little different syntax because
the fields are named a bit different.

How come this formula won't fly in Access?

I'll keep on till I get this right, no matter what.

Russ

 

[Russ from Apple Valley, Ca]

alrighty then aaron, ya got me.

about '96 or so (that's in the 20th century btw) I once printed an sql
manual. all of 1200 pages on my 10 pin. it sat on my shelf for 6 years, I
couldn't look at it anymore, and into the circular file it went.

I have done a little research about it today and I'll keep it up till I
understand SQL.

wish me luck.

Russ

 

[John W. Vinson]

I disagree. What happens if the formula changes next year? What if
there is a special deal worked out? Cost and selling price are the
exceptions to the normalizing rule, although they aren't really
exceptions, as they are the cost and price in effect at the time of
the sale.

Tony

OK here's what I've tried so far in Access and so far I haven't gotten it
right...

=IF(AND([PARTCOST] >0.5,[PARTCOST]<=400),[PARTCOST]
*2,IF(AND([PARTCOST]>400,[PARTCOST]<=1200),[PARTCOST]*1.8,IF([PARTCOST]>1200,[PARTCOST]*1.5,"Too Small")))

I'm using data from a table that has about 500 records. I'm trying to get a
form to do these calculations.

I've tried putting this formula into an unbound field, and into the field
where I'd normally look for the calculated value, of course one at a time.

I've made sure to have the three tables in the database linkd by
relationships.

I did get the excel formula to work with a little different syntax because
the fields are named a bit different.

How come this formula won't fly in Access?

Because Excel and Access have different language conventions. What you posted
is correct for Excel, but Access won't recognize it.

If you want to hardcode the cutoff limits and factors in your expression -
which as noted is probably A Bad Idea, they should preferably be stored in
another table - you can use the Switch() function instead of nested IIF's
(it's Immediate If, IIF(), in Access, rather than IF() as in Excel). The
Switch() function takes arguments in pairs; evaluates the pairs left to right;
and when it first encounters a pair with a TRUE value for the first element it
returns the second element and quits.

[PARTCOST] * Switch([PARTCOST] <= 0.5, {whatever you want for cheap parts},
[PARTCOST] <= 400, 2,
[PARTCOST] <= 1200, 1.8,
True, 1.5)

You can't multiply a PARTCOST by "Too Small" nor can you store "Too Small" in
a numeric field, so you'll need to come up with some reasonable alternative.

Try opening the VBA editor (click Ctrl-G is one way), press F1 to get the VBA
help file, and look at the Help for IIF and for Switch.

 

[George]

Tony, I wholeheartedly agree that SellPrice (or as I prefer, SalesPrice) is
NOT the same thing as ListPrice, or PartCost or anything else for that
matter. And I don't think it's necessary to consider them exceptions to the
rules of normalization.They are, by definition, different.

That's the case because SellPrice, or SalesPrice, is ALWAYS determined
within the context of a transaction. You can't have a sell price without
making a sale. In both cases, the complete value of either a price or a cost
can only be determined when the proper TIME element for the transaction is
considered.

Moreover, the ListPrice for any item can only be specified when an "As Of"
date is known for that ListPrice for that item. Even when there is no
negotiation involved in determining a sales price in a particular
transaction, it is impossible to state with precision what that List price
is without including the date that list price was in effect. Drive by the
corner gas station and take note of the price board out front. That price is
the "List Price for a gallon of gasoline as of the current date and time."
It clearly is not the same as the List Price for gasoline last summer, or
twenty years ago. It's only relevant to gasoline currently being sold. It
has, in other words, a time element to it. You have to know "when" in
addition to "how much" to have a valid definition of the sales price in a
transaction.

All list prices are sequentially variable, and can only be adequately
defined by including an "As Of" date in the table where parts and list
prices are recorded.

Let's take another example, to illustrate a different, but related, aspect
of sales pricing: an automobile dealership. The "List Price" of any vehicle
on the lot is generally regarded as the starting point for negotiations.
It's highly unlikely that any two people shopping for a new car will pay
exactly the same price for a vehicle of the same make and model on the same
day, regardless of the List Price. In addition to the "As Of" time element
in that List Price, there is a "Negotiated Discount or Surcharge" element to
it. The amount of that discount or surcharge is determined by the parties to
the transaction as they conclude the transaction.

As I see it then, list prices for items which are sold by negotiation are
both sequentially and concurrently variable. The amount of the sequential
variance is determined by the date of the transaction; the amount of the
concurrent variance is determined as a negotiated surcharge or discount to
the List Price during that transaction.

In both cases, recording the "List Price" of an item in a transaction,
without regard to the "As Of" and, optionally, the "Negotiated Discount or
Surcharge" components of the transaction, would be an incomplete.record of
the transaction.

Therefore, I would argue that "SalesPrice" is a required, element of any
transaction table. HOW that value gets recorded is a different question, of
course. And that's the subject of another session on the soapbox.

 

[MikeJohnB]

Perhaps I shouldn't have offered the correction to the Excel Function, Seems
to have complicated things. However, I did spell out

In "Excel", you have missed the closing argument of the last If

Oh well, you can win some, you lose some I guess

Seasons Greetings

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

= IIF(PartCost<400, PartCost * 2, IIF(PartsCost > 400 and PartsCost <
1201, PartsCost * 1.8, PartsCost * 1.5))


I disagree. What happens if the formula changes next year? What if
there is a special deal worked out? Cost and selling price are the
exceptions to the normalizing rule, although they aren't really
exceptions, as they are the cost and price in effect at the time of
the sale.

Tony

OK here's what I've tried so far in Access and so far I haven't gotten it
right...

=IF(AND([PARTCOST] >0.5,[PARTCOST]<=400),[PARTCOST]
*2,IF(AND([PARTCOST]>400,[PARTCOST]<=1200),[PARTCOST]*1.8,IF([PARTCOST]>1200,[PARTCOST]*1.5,"Too Small")))

I'm using data from a table that has about 500 records. I'm trying to get a
form to do these calculations.

I've tried putting this formula into an unbound field, and into the field
where I'd normally look for the calculated value, of course one at a time.

I've made sure to have the three tables in the database linkd by
relationships.

I did get the excel formula to work with a little different syntax because
the fields are named a bit different.

How come this formula won't fly in Access?

Because Excel and Access have different language conventions. What you posted
is correct for Excel, but Access won't recognize it.

If you want to hardcode the cutoff limits and factors in your expression -
which as noted is probably A Bad Idea, they should preferably be stored in
another table - you can use the Switch() function instead of nested IIF's
(it's Immediate If, IIF(), in Access, rather than IF() as in Excel). The
Switch() function takes arguments in pairs; evaluates the pairs left to right;
and when it first encounters a pair with a TRUE value for the first element it
returns the second element and quits.

[PARTCOST] * Switch([PARTCOST] <= 0.5, {whatever you want for cheap parts},
[PARTCOST] <= 400, 2,
[PARTCOST] <= 1200, 1.8,
True, 1.5)

You can't multiply a PARTCOST by "Too Small" nor can you store "Too Small" in
a numeric field, so you'll need to come up with some reasonable alternative.

Try opening the VBA editor (click Ctrl-G is one way), press F1 to get the VBA
help file, and look at the Help for IIF and for Switch.