435,519 Members | 2,332 Online
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

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

 P: n/a 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. Nov 12 '05 #1
15 Replies

 P: n/a co***@muskoka.com (Colin) wrote: I have a query that calculates the selling price of products oncustomer orders. Selling prices are calculated based on the averagecost of the items when purchased.As I make new purchases, the product cost changes (as it should) toreflect the new average cost. However, the problem is that the sellingprice of previously sold items also change when new purchases aremade.Keep in mind that I'm not storing any calculated values anywhere. Howcan I overcome price changes on previous sales without storingcalculated 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 Nov 12 '05 #2

 P: n/a 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. 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 Nov 12 '05 #3

 P: n/a 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. Nov 12 '05 #4

 P: n/a In message , Colin writesI have a query that calculates the selling price of products oncustomer orders. Selling prices are calculated based on the averagecost of the items when purchased.As I make new purchases, the product cost changes (as it should) toreflect the new average cost. However, the problem is that the sellingprice of previously sold items also change when new purchases aremade.Keep in mind that I'm not storing any calculated values anywhere. Howcan I overcome price changes on previous sales without storingcalculated 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. Nov 12 '05 #5

 P: n/a 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? Nov 12 '05 #6

 P: n/a 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. Nov 12 '05 #7

 P: n/a 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. Nov 12 '05 #8

 P: n/a "Tony Toews" wrote in message news:39********************************@4ax.com... co***@muskoka.com (Colin) wrote:I have a query that calculates the selling price of products oncustomer orders. Selling prices are calculated based on the averagecost of the items when purchased.As I make new purchases, the product cost changes (as it should) toreflect the new average cost. However, the problem is that the sellingprice of previously sold items also change when new purchases aremade.Keep in mind that I'm not storing any calculated values anywhere. Howcan I overcome price changes on previous sales without storingcalculated 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. Nov 12 '05 #9

 P: n/a 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 ahistory table to record the selling price at any given time. Create aquery that calculates the current selling price. Make that into anappend query, adding one more record to the history table. That willalso generate a historical record of selling prices that might be usefulin 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. Nov 12 '05 #10

 P: n/a 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 Nov 12 '05 #11

 P: n/a 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" wrote in message news:... 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. Nov 12 '05 #12

 P: n/a 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 Nov 12 '05 #13

 P: n/a 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" wrote in message news:... 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. Nov 12 '05 #14

 P: n/a 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 Nov 12 '05 #15

 P: n/a 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. I appreciate everyones input. Cheers...Colin Nov 12 '05 #16

### This discussion thread is closed

Replies have been disabled for this discussion.