Percent Calculations

[Shep]

Pardon my ignorance, but I am having trouble with a simple calculation. I simply haven't had much experience with them.

Note: I have searched the archives and tried to understand it, but I think many of the responses assume more knowledge and experience than I possess.

All I want to do is calculate a Price, based on a Cost multiplied by a given Markup (Percentage).

Currently we do this with a calculator, very simple:

For a 25% markup....Cost x 1.25 = Price

I've stored the Markup value as a Single/Percent (25%)

I've stored the Cost as Currency.

In a query for display on forms, I'd like to calculate Cost x Markup as Price.

I'm attempting to configure Price with an expression in the Field row.

First of all, is using the Field row the correct place to write this expression?

Secondly, what should the expression look like? Obviously, syntax is a killer...

Thirdly (most importantly?), am I even storing the values correctly?

Thanks for any help, and for looking past my ignorance on such a simple thing.

Shep

 

[R. Hicks]

This should be a simple "field expression" in your query to calculate the needed result:

Price: Format([CostField]+([CostField]*[MarkupField]),"$#,##0.00")

But .... do not store this result .. calculate the result dynamically when needed.

HTH
RDH

 

[Jack Cowley]

In the Field (top) row:

Price: ([Cost] * [Markup]) *.01 + [Cost]

I assume that markup is not a decimal, but if it is change the code above accordingly. Also, you do not want to store this calculation. You store the Cost and the Markup with the record but not the total. You calculate the total as you need it.

hth,
Jack

 

[Shep]

Thanks for the fast replies, guys. I really appreciate that.

No, I never store values which can be calculated when needed, thanks for the reminder, though.

I'll work with the two suggestions.

Cheers!
Shep

 

[Shep]

Jack, you correctly assume that Markup is not a decimal, at least not at first glance.

If the (Single/Percent) field has 30.00% in it, for example, clicking on the field changes the display to 0.3

I tried it with 10, 20, 25, 30, 40, 50, etc....some of the values change when clicked on, others do not. I can't see a pattern there.

At any rate, I changed the Markup field to Single with no additional formatting. This seems to work fine, as well. It will make it easier for the users to enter the values also, as I am actually allowing them to create their own cost ranges with appropriate markups.

R. Hicks' expression works for me too with a slight adjustment:
Price: Format([Cost]+([Cost]*[Markup]*0.01),"$#,##0.00")

Jack's expression displays as Currency without using the Format function. I don't understand why, other than the fact that my Currency format on the Cost field must propogate throughout the calculation.

So thanks for the lesson in basics, both of you. I knew it was simple. The syntax for extracting the correct Markup out of the range based on Cost will be a bit trickier. I'll be using DLookup for that, as much as I dislike the thought of it (tip of the hat to Pat Hartman).

Shep

 

[Jack Cowley]

I find it easier for the user to save 'percentages' as whole numbers and then use a multiplier in the code. As far as the format is concerned take a look at the Format property of your Price column in the query as it is probably is set to Currency.

I am glad the Mr. Hicks and I were able to assist you.

Jack

 

[Shep]

Actually I did, Jack. The Price column is unformatted.

I must have anticipated your comment about storing whole numbers, because I had already decided that if this is working for me, I'd leave it in it's simplest form.

Many thanks.

 

[Jack Cowley]

Not sure how your query is showing currency for the Price as mine does not unless it is formatted, but why worry about such things if it is working?!?!??

You are welcome...

Jack

 

[Shep]

True enough, Jack.

Another question, if you please.

The DLookup slows this query terribly.

Any idea how I might extract the Markup more quickly?



Just to make things clear:
I have a table with 3 Cost ranges, currently.
Products which cost between 0 and 100 are marked up 35%.
Products which cost between 100 and 300 are marked up 25%.
And so on...

I'm using the DLookup to find the Markup "where cost is greater than the lower limit and cost is greater than or equal to the upper limit".

Slowwwwwww

 

[Shep]

Seems wasteful and redundant, but how about a Markup field in the Inventory table(s) which is calculated at the time the item is added to inventory?

A stored field would certainly speed this Price calculation.

Shep

 

[Jack Cowley]

Since markup is something that will change over time you should have your table where you save the current markup but you should also store the markup with the customers order, or whatever it it is. Today the markup my be 25% but next year it may be 27% so you want to save the 25% with the customers records so when you calculate old records you get the right price.

I hope that made sense and that I correctly understood your qusestion/comment.

Jack

 

[Shep]

That's helpful Jack, thanks again.

Actually we store the price paid for products in the InvoiceDetails
table so that historical information can be kept. We don't really care what markup was used to calculate their selling price.

I stored the Markup with the inventory item to test it, and the query blazes.

 

[Jack Cowley]

Congratulations! Glad you have it sorted and working the way you want!

Jack