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