?
Solved

Keep a track of past Access

Posted on 2014-09-25
7
Medium Priority
?
211 Views
Last Modified: 2014-09-26
Hello
How do I save history of a table? The DB is already used. Now I got this requirement to maintain some kind of history of table with columns like this:
Item | marketType |cost1 |cost2| cost3
I89 | wholesalers |78 |89|55
What best practice you guy did to keep history? How do I keep a history of things here? What are some of your best practices you think? Would you remove it from the current table and place it in another table?

thank you
0
Comment
Question by:Rayne
[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
7 Comments
 

Author Comment

by:Rayne
ID: 40345127
The three set of cost will change over time for a combination of item and market type….so what could be done to store all previous combination of item and the market type and their costs? Associated with a date when that price set got obsolete or no longer followed…the history is needed purely for a reference point like what was the cost three years ago for this ?
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 800 total points
ID: 40345798
One way to maintain the History would be to not change the entries but to create a new entry each time a revision is made. You would then need a revision date field as well.

This may then have an impact on further reporting but could probably get round it by filtering or similar for latest revision date.

Thanks
Rob H
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 40345806
If the past price question is going to be asked frequently, then Rob's solution would be the way to go.

If the past price question will be asked very seldom, you would get better performance by archiving rows before they are updated.  You will need to add an archive date column to the new Archive table.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 600 total points
ID: 40345835
Use Data Macros to store a row in a history table for every change made. This is basically what Rob wrote.

The advantage is, that you have only a simple logic to implement in the macro.
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 200 total points
ID: 40346080
Instead of fields like Cost1,Cost2, Cost3, it would be better to have a linked table where you could add a new price whenever it is needed, with a PriceDate field with a default value of Date() or Now().  The table would be linked to the main table by Item.  That way you would always be able to retrieve past prices.  The Price table could be displayed in a linked datasheet subform on a form displaying Item records.
0
 

Author Comment

by:Rayne
ID: 40346155
thank you EVERYONE :)
0
 

Author Comment

by:Rayne
ID: 40346174
Hello all

Here is one more question
http://www.experts-exchange.com/Database/MS_Access/Q_28525930.html

thank you :)
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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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