Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Keep a track of past Access

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
Rayne
Asked:
Rayne
4 Solutions
 
RayneAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
aikimarkCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ste5anSenior DeveloperCommented:
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
 
Helen FeddemaCommented:
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
 
RayneAuthor Commented:
thank you EVERYONE :)
0
 
RayneAuthor Commented:
Hello all

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

thank you :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now