Keeping history of record change

[leesah]

I'd like to create a trackable way of keeping history on what and who changed
the record. I am envisioning a process that would open the record and copy
the data into a new form. Once the user changes the data, it would then
commit the new record into the database with the user name and time stamp of
the change - and then flip an inactive flag on the original record. This way
we'll be able to see change over time and by who.

I thought of putting in another table, but was wondering if it would be
better to manage if it was in the same table.

So - - two questions:

1.) What is the best way to copy an existing record and allowing the new
copy to be edited?

2.) When keeping history, should I keep it in the same table or not?

 

[RuralGuy]

I'd like to create a trackable way of keeping history on what and who changed
the record. I am envisioning a process that would open the record and copy
the data into a new form. Once the user changes the data, it would then
commit the new record into the database with the user name and time stamp of
the change - and then flip an inactive flag on the original record. This way
we'll be able to see change over time and by who.

I thought of putting in another table, but was wondering if it would be
better to manage if it was in the same table.

So - - two questions:

1.) What is the best way to copy an existing record and allowing the new
copy to be edited?

2.) When keeping history, should I keep it in the same table or not?

Maybe MVP Allen Browne's solution will work for you.
http://allenbrowne.com/AppAudit.html
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[leesah]

Thanks for the site, but it's not exactly what I was hoping for. This
application will document our ETL process that moves data from one source
system to the datawarehouse. Because business rules and logic can change
over time, I need a way to track the record as it was originally. Then, if
there was a need to go back to the logic that it originally was, it would be
failry easy - just flip the active flag to 'Y'.

I guess I was hoping that it would be easier to do in access. So far, it
sounds like I'll have to manually write out the insert and update instead.

 

[RuralGuy]

Thanks for the site, but it's not exactly what I was hoping for. This
application will document our ETL process that moves data from one source
system to the datawarehouse. Because business rules and logic can change
over time, I need a way to track the record as it was originally. Then, if
there was a need to go back to the logic that it originally was, it would be
failry easy - just flip the active flag to 'Y'.

I guess I was hoping that it would be easier to do in access. So far, it
sounds like I'll have to manually write out the insert and update instead.

I think you've got the picture now.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.