Solved

locked value

Posted on 2013-05-16
5
219 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
  • 3
  • 2
5 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 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 92

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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