Querying my audit (history) tables - how to figure out what changed?

[cricketbird]

The main tables in my database have corresponding history tables. When records are changed, an insert query copies the entire changed record (table row) over to a new row in the correpsponding history table plus additional columns for DateChanged and WhoChanged that are autofilled.

For example, the history table for a table containing three fields: ItemID, ItemName, ItemPrice would have:

TblItems_History (design)
DateChanged
WhoChanged
ItemID
ItemName
ItemPrice

TblItems_History (datasheet)
10/1/2013 Bob 164 Apples $1.00
10/2/2013 Jane 164 Apple $1.00
10/3/2013 Sam 164 Apple $1.50

I want to generate a report that tells me which fields changed each day:

• On 10/2, Jane changed the ItemName from Apples to Apple
• On 10/3, Sam changed the ItemPrice from $1.00 to $1.50

I've found great info here , but those queries are a little too simplistic for my needs.

Basically, I think I need to take the difference between the most recent two changes for each item, but am not sure how to get that, or how I would take the results of that query and generate the appropriate report.

Does anyone have an example of how they would do this or an idea for an approach?

Thank you!
CB

 

[MarkK]

I think I would structure my audit table like this . . .

tblUpdateAudit
AuditID
UserID
DateTime
TableName
TableID (PK of the edited record)
FieldName
OldValue
NewValue

 

[cricketbird]

Thanks. That style of audit trail has its value (see the link in my original post for one of the clearest descriptions of the two main audit types I've found), but also many disadvantages for our other purposes. We really need to stick with the record-level auditing that we have chosen.

Given our current system, is there a way to compare the most recent two records for a given item and determine which changed? Since the fields store numbers, I was thinking we could subtract the final two and identify the fields that were not equal to zero...just not sure how to approach generating that query.

Thanks

 

[David R]

Does your audit table have a Autonumber PK? If so you should be able to look at Max(PK) and Max-1(PK) for the given item...

Vaporware SQL:

SELECT TOP 2 PK, Item 
FROM AuditTable
GROUP BY PK, Item
ORDER BY PK DESC;