Using relational database to track changes
I'm new to setting up relational databases. I have a database in place with one table that contains 85 fields of information. I want to be able to track every time a change is made within this table. I know how to create an auto enter modified by field and an auto enter modified timestamp field. Those two fields work just fine, but every time someone new makes a change, the modified by and modified timestamp is overwritten with the user name and time stamp of whoever last modified the record. I need a way to track every time a modification was made to the record and who modified the record. Nothing I have tried has worked. I'm thinking that I need a separate table that creates a new record each time a record in my original table is modified. In the new table I want to be able to see the unique value assigned to the record that was modified and a history of every time a modification was made to the record. I would prefer to have only one record in my second table for each record that was modified that listed multiple modifications. Help!!!