Originally Posted by
Ajax
There are four basic possibilities
1. a single record, updated for latest change (which is what you seem to be wanting). This isn't really tracking, just latest update
2. history - you have two tables, the current table and a history table. When a record is added/updated you have code to also copy the added/updated record to the history table
3. a variation of the above is to just have the history table, once a record is created, all update actions are changed to append actions so any change creates a new record. Requires more work to determine the current record (based on the latest date) but easy to list the history. Also can be more difficult if you are relying on an autonumber primary key for linking to other tables - this will change on each update.
4. an audit table - this stores what has be changed (and when and who) on a field by field basis - this is the most detailed but requires some work to 'reconstitute' a record to look at the history.
All these require code, usually in the form beforeupdate event. Be aware that each field has an 'oldvalue' property so you can compare with the current value to see if it has changed (and from what to what)
Which of these is right for you depends on what you want to do with the information once you have it and potentially what is practical if multiple tables are involved.