logging changes to a field

[rupert]

Each day I get the same table with certain fields of records for several
hundred companies potentially changing. Say of 100 records one or two values
in a field giving the credit rating (eg AAA, AA, A, BBB etc) will change each
day.

I would like to track these changes in another table which shows the date on
which the credit rating chages for each company. Over time this table would
provide a track record of credit rating histories.

What is the best wat to do this and is it possible to automate the
procedure. I was told this sort of thing is relatively eacy using stored
procedures in SQL, but I would like to do it in Access.

 

[Klatuu]

How are you getting these tables in?
Do the records in these tables update existing data or are the appended to a
table?
Is it just the one field you want to track?
What information do you want to store regarding the change?

This is not hard to do, but you need to know the answers to the questions
above to be able to structure a routine that will provide the functionality
you want.

Please post back your answers.

 

[David C. Holley]

I would spin out the credit rating to a separate table and fields for
Effective and Expiration Dates. That way would you have a full history
and should be able to minimize the amount of coding neccessary to pull
it off.

companyId rating EffectiveDate ChangeDat
DIS C 2/1/2005 2/15/2005

 

[Klatuu]

That should work pretty well, the only thing missing then would be the
changed from value. It would be necessary to trace back through every record
to find the sequence of changes. It would also be missing date/time stamp of
change and userid of change.

This is more a typical audit table. It usually has:
Table Name
Field Name
Old Value
New Value
DateTime stamp of change
user id who made the change

One other problem with your solution is with the rating in a child table,
logic would have to be included to ensure retrieving the current rating. Not
a big deal, but I would go for the audit table concept. That way, a module
could be designed that could be used for any table in the database where the
business rules require such auditing.

 

[rupert]

Thanks to both of you. I am not sure about the best way to code this in vba.
I dont want to keep the old table but guess I will need to have it so I can
identify which ratings have changed by comparing it with the current table.
Once I have found changes then I can delete the old table.

I think I should use commit transaction so that the changes only are made
when all recordsets have been checked.

Is there any sample code and do you have any further suggestions eg whether
I'm on right path?