Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

locked value

I wish to make sale database ( see attached testdb) . Problem is that when I change price of products in tblProducts , value of field price changes in all records ( new and previous) in qrySalesdetails .
I need advice as what I should do to keep saved records of sales with new and old prices .

While awaiting your reply,
Kind regards,
DatabaseTest.accdb
0
aleksmi
Asked:
aleksmi
  • 3
  • 2
1 Solution
 
Patrick MatthewsCommented:
The best way to handle this would be to remove the unit cost from tblProducts, and instead use an effective-dated table that holds the prices for your products based on an effective date.  It makes your queries more complex, but it also gives the flexibility to handle a range of historical prices for your products.

This is also one of the rare cases where I would recommend breaking the rule of "not storing derived data".  In a database in which you store something like historical sales transactions, it can be very useful to store the actual price paid on a given transaction, reflecting not only the base price in effect at that time, but also any discounts/adjustments that may have been applied at the time of sale.
0
 
aleksmiAuthor Commented:
Thank you very much .
It will work but I was looking for simpler solution like in "Northwind"  where price is kept in table Products but if there is new price in table OrderDetails both(old and new) are being kept. I hope my comment made some sense to you
0
 
Patrick MatthewsCommented:
Please see the second paragraph in my comment http:#a39171150.  What I discussed there is the same thing as you cite from the Northwind sample database.
0
 
aleksmiAuthor Commented:
Thank you. I will try to "break down" the details form Nortwind sample database in order to try to figure it out.
0
 
aleksmiAuthor Commented:
it seems that you solution http:#a39171150. is bit too much for me at this time. would you be able to advise me where i could  see recommended solution done step by step ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now