You may want to check the example in my introductory article on auditing:
http://www.utmag.com/May2003/Page18.aspTo answer your question, you can compare FieldName with OldVal("FieldName") for each field.
To know which fields changed, you have to loop through all the fields.
As to the table structure, I think you need quite a lot more fields: TableName, primary key value, User, DateTime of change, and changes. The latter one, you can either have a single memo field, with contents like the following:
Field1: aaa -> bbb
Field2: 52.3 -> 53.3
...
Or, you might store old and new values in separate fields, in a normalized structure.
>Hi everyone:
>
>Is there an easy way to note the changes in a table to another table without listing old values and new values for every field in the original table? Right now, when someone makes a change to a table, a trigger saves the old values for each field and the new values for each field to a new table. What I would like is a way to know which field changed and only have the old and new values for that field stored to a table (in other words, the table that stores the old and new values would only be 2 columns)
>
>Any suggestions? Thanks.
>
>Paul
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)