Storing Field Changes

[Guest_imported]

Hi,

I'm trying to create a database right now and trying to have fields store their changes. For instance, if their is an account number for a record and then someone changes the number, I want the filed to be able to store the number that was changed and when so that I can go back and see the history of that account number and all the changes that have been made to it. So, this storing or history must also be retrieveable for reporting purposes and be able to keep numerous changes.

Can someone help me please?!

 

[Schlogg]

Firstly, keep the changes in a separate file related to the base file.

The trick in the whole thing is to identify the fact that changes have occurred. This is quite tricky and can be really messy depending on the number of fields and the size of the database.
One approach I have used for a small file with relatively few (max 30) records and about 10 fields, when I know the user might be going to make changes is this -
There are two 'versions' of every field. These are the current value and the new value.
Initially, I copy the current value into the new value for all records. The user sees only the new values.
For each field there is a calculated flag - (new = current). This allows you to find all of the changed records. You can then create your change record and set the current values to the new values.
Hope this gets you started on coming up with a better solution, which I would love to hear.




If you have lots of fields, you might like to set up a SuperFlag which is a calculation - (Flag1 or Flag2 or Flag3 or ....)




Cheers,
Paul J.