keeping a history of Updated records

[toolUser]

Hi,

This is probably really simple.

When I import records to a table (Current_Table) that may or may not update certain fields, I want to have a copy of any records with those fields updated copied to another table (Archive_Table) before the record is changed.

There are seven fields in the table; one key field, two that change on every update (Date and Time), don't want to archive those. When an update changes any of the four other fields I want to write the whole record to the Archive_Table, then update the record.

I've thought of some very kluge ways to do this that involve archiving everything, but there must be an elegant technique out there.

Thanks,

Jerry

[Lee Smith]

Hi Jerry and welcome to the list,

If I'm understanding what you are wanting, it's to create an Audit Trail of your database. This topic comes up from time to time, in fact a search of the Forum will turn up this recent Thread. Take a look at it by Clicking Here

If this isn't what you meant, let us know.

HTH

Lee

[toolUser]

Thanks,

Looked at the samples, but there seems to be a problem with each. The archived data all appears to exsist in the same table.

If I have a table within which there is a key field where current data is in a one to one relationship, I can't also keep data that is one to many in that table. So I think I need a second table to to keep the one to manys.

What i want to do is, during a scripted import, examine the current import record, determine if a concatenated foreign key (eg. Field1+Field2+Field3) for a given key is the same as the equivalent concatenated key in the exsisting record and, if not, write it to a one to many table.

Thanks,

Jerry

[Lee Smith]

What i want to do is, during a scripted import, examine the current import record, determine if a concatenated foreign key (eg. Field1+Field2+Field3) for a given key is the same as the equivalent concatenated key in the exsisting record and, if not, write it to a one to many table.

This doesn't sound like your original request at all.

Have you looked at this:

Open the Online Help and type in

importing data, About updating matching records while importing

Lee