By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,079 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,079 IT Pros & Developers. It's quick & easy.

How to update a price but retain old price for older records

P: 37
Hi everybody,

I've actually got my little database working (thanks to the scripts crew).

But now I have to change some prices on some items, the thing is I dont want to change the price of any historical records, only new records should have the new price. I made a query to specify which product should have the new price after a certain date, and then changed it to an update query, and added the new price in the "update to" field which seems to work fine. Then I appended the query to my main form query. But it seems to have changed the price of the product thru all records. I know I'm missing somthing. Can someone point me in the right direction please.

Cheers Eddie
Feb 2 '08 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,679
Hi everybody,

I've actually got my little database working (thanks to the scripts crew).

But now I have to change some prices on some items, the thing is I dont want to change the price of any historical records, only new records should have the new price. I made a query to specify which product should have the new price after a certain date, and then changed it to an update query, and added the new price in the "update to" field which seems to work fine. Then I appended the query to my main form query. But it seems to have changed the price of the product thru all records. I know I'm missing somthing. Can someone point me in the right direction please.

Cheers Eddie
It appears as though you made Price Updates on a Pricing Table which may be involved in 1 or more Relationships with other Tables (Referential Integrity enforced). If a Query is now created based on the Pricing Table, 'ALL' Records would reflect the change. Of course, this is only a guess without having the benefit of the DB in front of me.
Feb 2 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
If I've read your post correctly, you're goinng to have to do this Update Query thing every time a product price changes, which could get quite tiresome! This is one of the situations where many people who do accounting and/or inventory databases on a regular basis break one of the Cardinal Rules; they store calculated values! Most of them figure that doing so, in the long term, is much easier than the alternative!

The alternative is to have a table with products, prices and effective dates for those prices! This sounds simple enough, until you realize that if you pull up an invoice for 12/30/2007 for the XYZ Restaurant, and they had
75 items delivered (New Year's Eve is only a day away, after all!) your query is going to have to go to your
products/prices/effective dates table for all 100 items and check thru all the entries for each item until they find the price for each item on 12/30/2007!

If I were you, I'd add a field to my table for the calculated item prices, do an Update Query once, to store the cost before any prices changed, then start storing the item costs.

Of course, that's just one man's opinion! There'll probably be some old school/hardliners come along to shout "Oh, no! Don't store calculated values," but rules are like pie crusts, they're made to be broken!

Welcome to TheScripts!

Linq ;0)>
Feb 2 '08 #3

P: 37
Thank you both for the guidance and insight, its been a great help.
Cheers Eddie
Feb 3 '08 #4

NeoPa
Expert Mod 15k+
P: 31,709
If you have products and transactions, then the products have (current) prices associated with them and the transactions have historical prices associated with them.
You would never look at the current product price for historical transaction prices.
Feb 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.