Solved

locked value

Posted on 2013-05-16
5
221 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

820 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