Solved

locked value

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now