David,

We also have a grid that displays the full record. I would love to know the code you use in your grid to highlight the changes. We wanted to do that here, although I am not quite sure how to do it.

Also, we don't need the current version cause the most recent version in our _hist table (the audit mirror) always matches the current version of the table. We did it this way so we wouldn't have to do that UNION when displaying audit (we call it history).

Here is the main problem... lets say the user wants to see all the changes made by user 'joe' on Monday. We can get the data based on the change date = monday, and the report can compare the first record to the next record. But, what about the first record that matches the query, there is no previous record.

>We also have an option to only display columns for which at least one change has occurred.

Can you explain that a little more? Aren't there different columns changed in each row? I guess I don't know what you mean?

One other thing we are going to implement is what I call 'right-click audit' where when the user right clicks on a field in a form it will show a grid of all the changes with Date, Value, User.

How do you/your users deal with fields that are FK's? Do you have some way to show them a corresponding code or whatever?

Thanks for the message,
BOb


>Bob,
>
>If I understand you correctly, we're using almost the same approach for audit trails. This is not exactly the answer to your question, but here's how we use the audit trail:
>
>Basically, the audit trail table contains all the modified data and the base table contains the current version. Therefore, to get the entire history of one item we have to use a UNION. Something like this:
>
>

>Pseudo Code:
>
>SELECT * FROM AuditTrail
>UNION
>SELECT 'C' AS cTrnsType, -- transaction type C=Current, I=Insert, U=Update, D=Delete
>   GetDate() AS LastUpdate,
>   PlaceHolderFields that don't exist in base table,
>   BaseTable.*,;
> WHERE BaseTable.PK IN (SELECT PK FROM AuditTrail)
> ORDER BY PK, LastUpdate
>
> + optional WHERE and ORDER BY clause
>

>
>This query gives you the entire history for one item ordered by change date. Not side-by-side as you've requested. In our front-end, we have a form that displays this result set in a grid. To make it easier for the user to spot changes, we highlight each value that has been changed from the previous row. We also have an option to only display columns for which at least one change has occurred.
>
>I'm sure this concept could be easily deployed for a report.
>
>Hope this gives you some ideas.
>
>>Hi All,
>>
>>Ok, here is what I need help with. I have a table which is audit data of a parent table. Basically, it is the same schema as the table it audits with a few fields added, audit_id, audit_datetime, audit_imageType, audit_username.
>>
>>How can I query this data so that I can join a record to the record that comes before it cronologically where the key of the record it audit matches. I want to do this so I can get the old value and new value for an audit report.
>>
>>Any ideas? Here is a small snippet of one of the audit tables, ee_id is the primary key of the table.
>>
>>audit_id
>>, audit_datetime
>>, audit_imageType
>>, audit_username
>>, ee_id
>>, empnum
>>, fname
>>, mname
>>, lname
>>
>>So, I want to end up with a return of:
>>
>>audit_datetime
>>,audit_username
>>,ee_id_old
>>,ee_id_new
>>,empnum_old
>>,empnum_new
>>,fname_old
>>,fname_new
>>etc...
>>
>>Thanks,
>>BOb