Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Customer Pricing < Backup | Record Locks >
Back to Customer Pricing    Comments List
Pricing Increases Upload Images   Link   Email  
Jenny Bisset      
2 years ago
Hello there, I hope that everyone is well.
I have a "Consumable Stock" database that is used to track incoming consumables (ordered from suppliers) and consumables issued (to employees)
My company uses this to track all kinds of this, including to track how much they spend on items such as PPE and Consumables.
I basically have a "Purchase Order" Form to track stock ordered and an "Issued" form which track stock issued. However, in my inventory table, the price is also included there, as prices are pretty much standard. However, we have annual price increases.
How can I update the new prices without also changing the prices in the "Purchase Orders" and "Issued" tables?
I still need to pull reports such as our year to date spending on certain items (eg. PPE) ...but if I change the price in my Inventory table, it is going to change all of the prices in all of my reports?
Scott Axton            
2 years ago
Jenny Bisset      
2 years ago
Hi Scott,

Thank you so much for the quick reply. However, I have seen that video. However, that video only shows me how to change the pricing automatically (which is pretty nifty)...but (unless I missed something or didn't understand it completely, which is very possible) - that video doesn't show how to keep the records that have already been created with the old pricing. it will still change all of the data in my database to the new pricing.
So, when I pull a spending report over the last 3 months, it will calculate all the items purchased at the new won't calculate 2 months at the old price and 1 month at the new price...
Can you perhaps show me how to do something like that?
Scott Axton            
2 years ago
How are you storing the price in your tables?  In the Invoicing video Richard shows how to disconnect the price from the records and put the records into their own table.
That way you can maintain the pricing in historical orders and when the price changes you will automatically get the new updated price.

If that one is still not what you are looking for let me know - I may not be understanding what you are asking correctly.
Kevin Yip       
2 years ago
Hi Jenny, in my old job, we kept prices in their own separate tables: inventory prices in inventory table, order prices in order table, product "standard" prices (MSRP) in the product table, etc.  This is the same thing Scott alluded to.  Sounds like you don't have this set up, and only have one place to get the prices from.  "Prices" is something you can't normalize in this scenario, because they are different in different circumstances.  A product's standard price may be $5, but you can sell to a customer at a discount at $3.  You can sell to yet another customer at $4.  And you can invoice it at yet another price.  In the retail/supply world, there could be a million different "prices" for any given item.  

P.S. The only price that is constant is the cost of manufacturing an item (if you are a manufacturer).  I worked in the garment industry, where products were seasonal, and the manufacturing cost was pretty much constant for 1-2 seasons.  But if you are in an industry where products have really long shelf life, then obviously the cost of making an item 10 years ago would be different from, say, 6 months ago.
Jenny Bisset      
2 years ago
Hi Kevin,

Thank you so much for your reply, it is much appreciated.
I don't really work with "selling prices" as we do not sell any of the consumables that we buy. We are a steel manufacturing company, so we basically buy PPE and consumables and issue them out to employees to manufacture steel products.
But, our company does want to keep track of how much money we spend on PPE and Consumables and keep the spending within bounds. I also keep stock of these items in my main store.
So, basically, my boss wants to be able to see a report on what we have spent PPE and consumables per quarter or per month or so.
I have a products table containing all the information on the producs (ID, Name, Category, Description, Supplier, Price, Reorder Level, Qty on Hand, Etc.) and the prices are mostly consistant. However, we do get price increases on some of the products from time to time. But now, if I change the price in my table to the "new" price...then that is what will be pulled through on all of the other tables.
Is there a way that I can keep the stuff that I ordered at the old price on my Purchase order from changing to the new price so that when I pull it into a query that it gives an accurate reflection of our total spend for year to date,  for example? Or is there no way that we are able to do that in access?
Scott Axton            
2 years ago
Jenny -
This is a totally unrelated example but hopefully this will convey the concept.  

Let's say you have a garden and in it you have put in some tulip bulbs.  In the spring you take a picture and the leaves are just coming up.  2 weeks later you go out to your garden and take another picture.  You notice this time that there is a bud on your tulip.  Another 2 weeks pass and you take another picture.  This time the bud has opened and the tulip is in full bloom.  Then you go out the next week and the tulip flower is fading and almost dead.

If you had taken the picture and placed it in your photo album with the same name on your computer the 2nd week all tulips would look the same.  Same thing for each of the other photos.

Now on the other hand if you had placed a copy of each photo into your scrap book, the photos would still have the same name but you could see the progression of the tulips growth over time. A history so to speak of the growth.

I hope that make a little sense.  

So by now you are probably saying, "I sure hope Scott gets help with his drinking issues."  But bear with me.

So, the same thing needs to occur with your data base.  Whether you are selling products or ordering products or what ever you are "taking your picture" of the item as it is right now.  Then, depending on how you store the picture, is how your data "album" will look in the future. Are you overwriting the picture or keeping a copy of it?

The way you are set up right now you are overwriting the picture of the data instead of copying the data into your new record.  In the invoicing video I pointed you to, Richard shows you how to take this same concept into account by using a product table and "taking a picture of it" to insert into your order detail table.  Making it so the history is preserved for the order.  Not changing each previous order each time you change the price.

Don't get hung up on what you call the data: Invoice, Purchase Order, etc. - the concept is still the same.
In the first video, the free version lesson, Richards shows the overwrite style.  In the extended cut he shows how to split off the products and put it into the order form so that you are keeping a copy of the "picture" as it exists today.
Jenny Bisset      
2 years ago
Hahaha! Thanks Scott, you made my day.
Okay, I will watch the video again and try to see the flowers the same way that you do. Let me watch the extended cut again and get back to you. Thanks for the help (and the laugh) is much appreciated. I will be in touch soon &#9786;
Kevin Yip       
2 years ago
Hi Jenny, whether you deal with buying or selling prices, the concept mentioned in my previous post still applies: you need prices in your order table, not your product table.  Your order table should contain data like the following, including all pertinent order info such as date of purchase, units purchased, unit price, etc.:

On 10/25/21, purchased 500 units at $2 each from Supplier A.
On 1/10/22, purchased 600 units at $2.10 each from Supplier B.
On 5/1/22, purchased 550 units at $2.50 each from Supplier C.

Then the total cost you paid is: (500 units x $2) + (600 units x $2.10) + (550 units x $2.50) = $3635.

This calculation can be easily done with a summation query on the order table:

SELECT Sum(Units * UnitPrice) FROM OrdersT
Jenny Bisset      
2 years ago
Hi Kevin,

That makes sense...thank you. But, I want to know - do I then have to enter the price manually for each product that I order/issue? Because we go through hundreds of products per day and having to look up each price for every product might take long.
Kevin Robertson            
2 years ago
Store the current price for each product in the Product Table. When you add the product to an order also add the price for that product. When the price is updated in the Product Table, previous orders won't be affected by the price change.

Richard shows a good example of this in the Extended Cut of the Invoicing video.
Kevin Yip       
2 years ago
HI Jenny, yes, you do need to put individual prices for each and every individual items.  That is not only the usual business practice, it is also the industry standard.  In my old job, we sent electronic orders and invoices, and they had to conform to the standard industry formats, which required a price for every line item.  The pictures below are from my old job.  We entered a price for every item on the order form.  And when we invoiced exactly the same items for the same order, we entered the prices all over again on the invoice.  That's just how it's done.  We had ways to automate those entries, of course, so we didn't actually have to manually enter them all.  You can do the same.  You can use DLookup() and/or update queries to automate the process of filling in the prices.
Kevin Yip       
2 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Customer Pricing.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/21/2024 7:13:42 PM. PLT: 1s