Solved

Keep a track of past Access

Posted on 2014-09-25
7
205 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
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 32

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 33

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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 …

772 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