adding information to another table...

[pablavo]

Hello, I have a question... unsuprisingly

In my DB, which is for employees and the positions they hold, I have to let users assign roles to existing employees, like a staff rotation.

One person leaves which can start a domino affect of new role (position) assignments.

So, I've used a continuous form with dropdown lists of all roles so employees can be assigned their new role.

I would like, if possible, to have another table that stores information on these changes everytime they happen. This table will hold four fields:

EmployeeFirstName,
Lastname,
OldRole,
NewRole

So, the table will store the role value before the change was made and it will also store the new value. (I'm really not sure if storing the OldRole is possible since it's the same field that will store the Newrole)

This is so Users can view any changes made to the DB, especially to quickly correct any previous mistakes.

Does anyone know if this is possible and how it would be done?

Thanks

 

[namliam]

Yes it is possible, have a search for "audit trail" and you will find some samples.

 

[pablavo]

thanks NamLiam, you've been a great help. I'll have a look at some of the methods and let you know how I got on​