Best way to track data changes

Posted on 2008-10-25
Medium Priority
Last Modified: 2013-11-29
I am looking for the best way to track data changes. Basically I will have some users entering data into a form but on a weekly basis I have to QA the data. What I want to be able to do is change the data but still keep the old data so the end user can see what the data is changed from and changed to.

My table has 2 fields that would require changes named Process and Cause. I would like to create 2 new fields called qaProcess and qaCause that would store the old data. The correct (or corrected) information should remain in the original Process and Cause fields for reporting purposes. What is the best way for me to move the incorrect data from one field to another. Is this the best way to go about it? Should I consider using a separate table to capture this data?

Only about 1 in 20 records would require this change.

Any help would be greatly appreciated.
Question by:davidlittle2008
LVL 18

Expert Comment

ID: 22804417
Hi davidlittle2008,

If it were my db I would create another table with on the PK and the two changed columns.


LVL 42

Accepted Solution

dqmq earned 375 total points
ID: 22804800
In DW parlance, your approach is called a type 3 changing dimension.  It's a perfectly valid solution if it meets your needs.  However, understand that it is limited to storing only the last change and you will not know who did it or when it was done.  Using another table provides a more robust audit history.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

597 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