Best way to track changes in a table

hibbii

Hi all,

I am currently working with a database that has one main table. In this table (from period to period) there may be certain changes in the accounting data.

What would be the best way to incorporate the changes but at the same time keep the historical data to be able to go back on and compare periods?

And what would be the best way to find these changes from any given period to another?

Thanks for any input.

PEB

I'm affraid to bother you with my questions, but what kind of accounting data concerns your table!

A table with accounting queries for a period or a table with the primary accounting documents that are used for reports?

msquared

Quote:

Originally Posted by hibbii

Hi all,

I am currently working with a database that has one main table. In this table (from period to period) there may be certain changes in the accounting data.

What would be the best way to incorporate the changes but at the same time keep the historical data to be able to go back on and compare periods?

And what would be the best way to find these changes from any given period to another?

Thanks for any input.

One possibility is to export the data to a spreadsheet with a timestamp before making changes. The spreadsheets can then be linked to the database if required. Linked spreadsheets can be used in queries, etc. for data comparisons. This should work especially if you export the field names as headers.

All of this can be coded into VBA depending on your requirements; e.g. how often is the data updated. Would all the data need to be permanently linked or would a list of the filenames and a command button to link it for specific purposes suffice. If you do this the spreadsheets would need to be protected from user interaction as they could edit the structure or values.

The other main questions really is what do you want to do with this data on an ongoing basis. Just view it or use queries for data comparisons.

Killer42

One method used in an Access project I worked on years ago was to have a "log" or "history" table corresponding to the main table. It had duplicates of all fields, plus some field (most likely an Autonumber) to keep them in sequence.

Every time a record is created or updated on the main table, you copy it to the history table (including any extra info you want, such as user, date/time etc.)

To keep the space requirements from blowing out too rapidly, we used a compression technique on the history table. For each main-table record, there was one full initial record in the history table. All subsequent entries for that record stored only the values which had changed from the prior entry.

This did have certain advantages in storage, of course (reduced the storage by a huge amount, actually) but made processing more complex. For one thing, to rebuild any history entry, you have to start with the initial entry from when that record was created, and run forward through its history to the point in question.

So, the whole compression thing may be more trouble than it's worth. But dumping a copy of your record to a history file every time it changes (including initial creation) does provide you just about the ultimate in accountability/reproducability.

PEB

In fact the proposal of Killer is very good...

If i can suggest you, it should be better to have a different structure from the main table, only those fields:

LogID
Table
ChangedFieldName
New introducedValue /Or OldIntroducedValue/
DateStamp
User

And insert in this table all changes in the fields...
And this shouldn't get a lot of place than needed

But my previous question about your need is always here. If in this table you want to keep the chronologie of your reports, so the method of Mary is convenient...

If you want to track the changes in your primary documents introduced in the respective table, so this method suits more...

It's you to decide!

Quote:

Originally Posted by Killer42

One method used in an Access project I worked on years ago was to have a "log" or "history" table corresponding to the main table. It had duplicates of all fields, plus some field (most likely an Autonumber) to keep them in sequence.

Every time a record is created or updated on the main table, you copy it to the history table (including any extra info you want, such as user, date/time etc.)

To keep the space requirements from blowing out too rapidly, we used a compression technique on the history table. For each main-table record, there was one full initial record in the history table. All subsequent entries for that record stored only the values which had changed from the prior entry.

This did have certain advantages in storage, of course (reduced the storage by a huge amount, actually) but made processing more complex. For one thing, to rebuild any history entry, you have to start with the initial entry from when that record was created, and run forward through its history to the point in question.

So, the whole compression thing may be more trouble than it's worth. But dumping a copy of your record to a history file every time it changes (including initial creation) does provide you just about the ultimate in accountability/reproducability.

Killer42

Quote:

Originally Posted by PEB

In fact the proposal of Killer is very good...

Thank you.

Quote:

Originally Posted by PEB

If i can suggest you, it should be better to have a different structure from the main table, only those fields: ...

Yeah, that's a good idea. Using a generic table where you store the table name, field name and changed value seems as though it would be more widely applicable without so much tailoring. We had to tailor each history file to fit the corresponding table.

Also, because you are only storing details for each changed field, you would not need to consider the compression technique we applied. (Interestingly, since we were blanking out unchanged fields in each step in the history, we had to come up with a way to represent fields whose value changed to blank.)

I do think that our technique would save some space, but that's rarely a major consideration these days. I dislike the old "disk space is cheap" saying, but it is largely true.

hibbii

Thank you all. I will try some of these suggestions and let you all know how it goes.

Best way to track changes in a table

re: Best way to track changes in a table

re: Best way to track changes in a table

re: Best way to track changes in a table

re: Best way to track changes in a table

re: Best way to track changes in a table

re: Best way to track changes in a table

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about