Copying the row above for editing?

[NewShoes]

Copying the row above for editing (to track changes)?

Hey all,

At the minute I have an employee database made up of 5 tables. I have a query that pulls certain data from each table and displays it in a nice neat table.

Here is the tricky bit...Now I need to keep records of all previous "versions" of the data when it's changed. So for example, If I change someones working hours from 37 to 16, I need the row above to show 37 and the newest row below to show 16(thus letting me track all changes).

I am planning on having soem kind of date/time field so I can query just for the latest version of the record.

Is this easily do-able?

Many thanks in advance

-NS

 

[DCrake]

First of all you need to decide which fields if changed warrant copying to an archive table. Some changes may not be relevant and you will end up with a massive amount of versions most of which were not needed. Things like spelling mistakes in text.

Also if you clone the record and save the changes you need to somehow identigy what actually changed. What the old value was compared to the new value, and why it was changed, Who changed it, and when it was changed.

 

[pbaldy]

It's probably possible, but I'd look at audit trail techniques and see if that does what you want. The two basic methods I've seen are a memo field containing a record of changes, and an audit log table contain a record for each change (personally I prefer the latter). Searching on "audit" should turn up some discussions.

 

[NewShoes]

Thanks for your quick reply David!

I was planning on having a "comments" field where the user could enter what was changed and why.

Is there a simple way to clone the data or would it be a manual copy and paste job?

Thanks,
-NS

 

[DCrake]

Having a comments fields and relying on the user to enter the correct info is a bit suspect and open to all sorts of issues, like lies, ambiguities, errors, etc. By doing it in code at least you know waht has been recorded is correct. Remember GIGO (Garbage In Garbage Out). Or as I say SISO.