?
Solved

locked value

Posted on 2013-05-16
5
Medium Priority
?
224 Views
Last Modified: 2013-05-19
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
Comment
Question by:aleksmi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 39171150
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
 

Author Closing Comment

by:aleksmi
ID: 39171420
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39171569
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
 

Author Comment

by:aleksmi
ID: 39173875
Thank you. I will try to "break down" the details form Nortwind sample database in order to try to figure it out.
0
 

Author Comment

by:aleksmi
ID: 39178672
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question