Solved

Keep a track of past Access

Posted on 2014-09-25
7
208 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 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
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.

 
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

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 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