HELP.... How to avoid updating selling prices?

[Colin]

I have a query that calculates the selling price of products on
customer orders. Selling prices are calculated based on the average
cost of the items when purchased.
As I make new purchases, the product cost changes (as it should) to
reflect the new average cost. However, the problem is that the selling
price of previously sold items also change when new purchases are
made.
Keep in mind that I'm not storing any calculated values anywhere. How
can I overcome price changes on previous sales without storing
calculated values?
I would appreciate any ideas on this. Thank you.

[Tony Toews]

co***@muskoka.com (Colin) wrote:

I have a query that calculates the selling price of products on
customer orders. Selling prices are calculated based on the average
cost of the items when purchased.
As I make new purchases, the product cost changes (as it should) to
reflect the new average cost. However, the problem is that the selling
price of previously sold items also change when new purchases are
made.
Keep in mind that I'm not storing any calculated values anywhere. How
can I overcome price changes on previous sales without storing
calculated values?

In this situation you should be storing the cost and price at the time of the sale.
This is not a violation of the normal forms because the cost and price of the
inventory item can change. You need to record what it is at that moment in time.

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

[Colin]

Thanks Tony.
I understand the logic of what you are saying but how do I store the
result of each query (i.e. the calculated cost and selling price) into
a table. Obviously I need to create fields in the Customer Sales table
for these items but how do I get the query results into these fields?
Colin

PS: I'm a new to access and database applications. VBA code is not one
of my my strenghts at this time. <g>

In this situation you should be storing the cost and price at the time of the sale.
This is not a violation of the normal forms because the cost and price of the
inventory item can change. You need to record what it is at that moment in time.

Tony

[Salad]

Tony Toews wrote:

In this situation you should be storing the cost and price at the time of the sale.
This is not a violation of the normal forms because the cost and price of the
inventory item can change. You need to record what it is at that moment in time.

Tony

I've seen pro developers do that...use calced fields...not sure how
professional there are. I often wondered what happend in YTD reports as
prices changed. They could owe some wopping taxes if they used those
figures for the tax calculations.

[Bernard Peek]

In message <b1**************************@posting.google.com >, Colin
<co***@muskoka.com> writes

I have a query that calculates the selling price of products on
customer orders. Selling prices are calculated based on the average
cost of the items when purchased.
As I make new purchases, the product cost changes (as it should) to
reflect the new average cost. However, the problem is that the selling
price of previously sold items also change when new purchases are
made.
Keep in mind that I'm not storing any calculated values anywhere. How
can I overcome price changes on previous sales without storing
calculated values?
I would appreciate any ideas on this. Thank you.

It's quite OK to store calculated values. You might be able to use a
history table to record the selling price at any given time. Create a
query that calculates the current selling price. Make that into an
append query, adding one more record to the history table. That will
also generate a historical record of selling prices that might be useful
in predicting future prices.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Colin]

Here's something I tried - that did not work:

I created a new field item in my customer table called SellPrice.
Then I put a texbox on the SalesItems Subform and set it's Control
Source to the new table field, SellPrice.
Finally, I wrote the expression to calculate the selling price in the
Before Update Event properties of the new text box.
Me!SellPrice = Me!ItemQty * Me!ItemUnitCost

The result showed #Name?

What am I doing wrong?

[Salad]

Colin wrote:

Here's something I tried - that did not work:

I created a new field item in my customer table called SellPrice.
Then I put a texbox on the SalesItems Subform and set it's Control
Source to the new table field, SellPrice.
Finally, I wrote the expression to calculate the selling price in the
Before Update Event properties of the new text box.
Me!SellPrice = Me!ItemQty * Me!ItemUnitCost

The result showed #Name?

What am I doing wrong?

Dbl-click on the field that contains the SellPrice. Click on DataTab.
Check the control source (press button on right to see if it is in the
query). Then click on the Other tab. Is the name SellPrice?

You can have the name of the field in the control source as one name and
the Name value in other...how you refer to it...with another name.
Sometimes it's best to use the same name.

[Colin]

I'm surprised that Access is so against storing calculated values that
are of historical nature (that would change if recalculated at a later
date).

What about using a textbox that is bound to the table field to update?
The textbox would be populated from a query which calculates the
current selling price and then this figure would be stored in the
table record.
Is there a way to accomplish this?

It's quite OK to store calculated values. You might be able to use a
history table to record the selling price at any given time. Create a
query that calculates the current selling price. Make that into an
append query, adding one more record to the history table. That will
also generate a historical record of selling prices that might be useful
in predicting future prices.

[CDMA Cop]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:39********************************@4ax.com...

co***@muskoka.com (Colin) wrote:

I have a query that calculates the selling price of products on
customer orders. Selling prices are calculated based on the average
cost of the items when purchased.
As I make new purchases, the product cost changes (as it should) to
reflect the new average cost. However, the problem is that the selling
price of previously sold items also change when new purchases are
made.
Keep in mind that I'm not storing any calculated values anywhere. How
can I overcome price changes on previous sales without storing
calculated values?
In this situation you should be storing the cost and price at the time of

the sale. This is not a violation of the normal forms because the cost and price of the inventory item can change. You need to record what it is at that moment in time.

Please ignore the above posting and others which are somewhat bizarre.

Note to persons new to this newsgroup. One person, with many identities, is
being
rather disruptive. If you see a bizarre posting it's probably the work of
this
disruptive person.

You can also change your NewsReader program settings to ignore off-topic
posts. See
http://www.hyphenologist.co.uk/killfile/ for more information.

[Salad]

Colin wrote:

I'm surprised that Access is so against storing calculated values that
are of historical nature (that would change if recalculated at a later
date).

What about using a textbox that is bound to the table field to update?
The textbox would be populated from a query which calculates the
current selling price and then this figure would be stored in the
table record.
Is there a way to accomplish this?

It's quite OK to store calculated values. You might be able to use a
history table to record the selling price at any given time. Create a
query that calculates the current selling price. Make that into an
append query, adding one more record to the history table. That will
also generate a historical record of selling prices that might be useful
in predicting future prices.

I don't get it. If you can recreate the prices at any time thru
calculations then stay with calculations. If you can't recreate the
selling prices via calculations then store the value. Nothing is
stopping you from doing whatever you want. But if prices change, how
can you ever know what prices you sold things at....except by going back
to receipts...hardcopy.

Some people will store the qty sold in one table and link to a prices
list for the item. So 3 * 1.50 is $4.50. Now we update the price to
$2.00 in the prices list table. Now when you run the report to see how
much profit you made, the total sale is now $6. You made a $1.50 profit
that you never realized by not doing anything but changing the price at
a later time. And since you now have no history of what the unit price
is you are now up a creek w/o a paddle.

Do what you want tho. Nobody will stop you from pursuing the things you
want to do.

[David W. Fenton]

co***@muskoka.com (Colin) wrote in
news:b1**************************@posting.google.c om:

I'm surprised that Access is so against storing calculated values
that are of historical nature (that would change if recalculated
at a later date).

Access is not "against" anything at all.

At the time the invoice item is created, you calculate the average
selling price and record it somewhere. I don't know if you're using
it for the suggested price or what.

Your whole scenario makes no sense to me.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Colin]

To calculate a line item selling price I have to take a number of
steps.
Selling Price is established by:

1) Calculate the average purchase price (adjusted for local currency).
2) Multiply Average Price by a mark-up factor to arrive at List Price.
3) Multiply list price X a customer type factor (e.g. End User or OEM)
to arrive at Client Price.
4) multiply the client price by the exchange rate factor (for sales to
other countries).
5) Apply a sales person level discount (like less 5% or 10%...)
6) Equals...Line Item Selling Price.

If the selling price where calculated 6 months from now (or 6 minutes
from now) it's not likely that the calculation would return the same
value. Hence, why I need to store the selling price.

Back to my original question.....How can the selling price be stored
in a table? Keep in mind that the selling price is the result of a
number of queries.

Thanks

"David W. Fenton" <dX********@bway.net.invalid> wrote in message news:<Xn**********************************@24.168. 128.86>...

co***@muskoka.com (Colin) wrote in
news:b1**************************@posting.google.c om: At the time the invoice item is created, you calculate the average
selling price and record it somewhere. I don't know if you're using
it for the suggested price or what.

Your whole scenario makes no sense to me.

[David W. Fenton]

co***@muskoka.com (Colin) wrote in
news:b1**************************@posting.google.c om:

To calculate a line item selling price I have to take a number of
steps.
Selling Price is established by:

1) Calculate the average purchase price (adjusted for local
currency). 2) Multiply Average Price by a mark-up factor to arrive
at List Price. 3) Multiply list price X a customer type factor
(e.g. End User or OEM) to arrive at Client Price.
4) multiply the client price by the exchange rate factor (for
sales to other countries).
5) Apply a sales person level discount (like less 5% or 10%...)
6) Equals...Line Item Selling Price.

If the selling price where calculated 6 months from now (or 6
minutes from now) it's not likely that the calculation would
return the same value. Hence, why I need to store the selling
price.

Back to my original question.....How can the selling price be
stored in a table? Keep in mind that the selling price is the
result of a number of queries.

Well, you store a quantity in the invoice line item, so add a field
for the price, and write the price to that record when it's created,
at the same time you write the quantity (and all other data in the
line item record).

That's what other folks told you.

I'm not sure I understand why that's a stumbling block for you.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Colin]

Thanks for your response Dave. Perhaps I did not adequately explain
the problem.
When customer orders are created, the line items ordered are recorded
using the order entry form by a combination of manually typing in
values and retrieving values that are calculated. The important fields
include:
Quantity Ordered (manually typed in).
Product Ordered (selected via combo box that looks up the product).
The item Selling Price (automatically entered via query that
calculates it).
A discount that's applied against the item selling price (typed in as
a percentage 5%,10%, etc.)

All values that are typed into the order form never change. The
selling price (not manually entered) changes whenever the cost price
changes (due to new purchases of that particular product). This
happens because the customer selling price of the product is
calculated up from the weighted average of the purchase price of the
product.

If the order form required manual entry of the selling price there
wouldn't be a problem recalling it in the future. However, since it's
calculated, it's subject to change. My goal is to use the calculated
price without having to manually type it into the selling price
control. Typing the value in can lead to errors and is not efficient.

If I could capture the selling price (at the time of order entry)and
store it without it being bound to the query that continually
recalculates it...problem solved.

Thanks
"David W. Fenton" <dX********@bway.net.invalid> wrote in message news:<Xn**********************************@24.168. 128.86>...

Back to my original question.....How can the selling price be
stored in a table? Keep in mind that the selling price is the
result of a number of queries.

Well, you store a quantity in the invoice line item, so add a field
for the price, and write the price to that record when it's created,
at the same time you write the quantity (and all other data in the
line item record).

That's what other folks told you.

I'm not sure I understand why that's a stumbling block for you.

[David W. Fenton]

co***@muskoka.com (Colin) wrote in
news:b1**************************@posting.google.c om:

Thanks for your response Dave. Perhaps I did not adequately
explain the problem.
When customer orders are created, the line items ordered are
recorded using the order entry form by a combination of manually
typing in values and retrieving values that are calculated. The
important fields include:
Quantity Ordered (manually typed in).
Product Ordered (selected via combo box that looks up the
product). The item Selling Price (automatically entered via query
that calculates it).
A discount that's applied against the item selling price (typed in
as a percentage 5%,10%, etc.)

All values that are typed into the order form never change. The
selling price (not manually entered) changes whenever the cost
price changes (due to new purchases of that particular product).
This happens because the customer selling price of the product is
calculated up from the weighted average of the purchase price of
the product.

If the order form required manual entry of the selling price there
wouldn't be a problem recalling it in the future. However, since
it's calculated, it's subject to change. . . .
What I'm saying is take the calculated value you say you have
"automatically entered via query that calculates it" and store it in
he line item.
. . . . My goal is to use the
calculated price without having to manually type it into the
selling price control. Typing the value in can lead to errors and
is not efficient.
I really don't see the issue here. Depending on how you're editing
the data, one way would be to write the calculated value to the
field you'll be storing it in using the invoice item form's
BeforeUpdate event. As you say you're using a combo box to pick the
item, its AfterUpdate event makes more sense (see below).
If I could capture the selling price (at the time of order
entry)and store it without it being bound to the query that
continually recalculates it...problem solved.

It's a triviality, really, and that's why there's been so much
confusion.

The way I'd normally do this would be with a price lookup in the
combo box that you're using for choosing the item (wouldn't that
make sense?). In the combo box's AfterUpdate, write the value from
the calculated price column of the combo box to the SellingPrice
field. Assuming a combo box with 3 columns, ItemID, ItemName,
CalculatedPrice, the AfterUpdate event would look like this:

Me!SellingPrice = Me!cmbItem.Column(2)

That's it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Colin]

Thanks!
My purchasing, inventory, BOM, routing, quoting, selling
(PIBRQS)database works great now. The selling prices don't even change
when the purchase prices do. <g>
I appreciate everyones input.

Cheers...Colin