Solved

Keep a track of past Access

Posted on 2014-09-25
7
210 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 200 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 45

Assisted Solution

by:aikimark
aikimark earned 100 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 150 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 50 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…

717 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