Query to show history of edited records

[tigersden]

Hi,
I have set up an audit table to track any changes to a Risk Assesment. Table structures below:
tblAuditRisk
ID PK
LoginName
TableName
RecordPKey
FieldName
OrigValue
NewValue
Date

tblRisk
RiskRef PK
RiskDesc
ReviewFreq
DeptRef
Process
AssessorRef
Task
PersonAtRisk
Comments
Method
ControlMeasure
Impact
Likelihood

What I am trying to do is write a query which would list each Risk Assesment from tblRisk (current assessment) & show all the amendents to it & base a report on the query result.
I ideally would like to be able to list the changed fields value under the respective column for that field in tblRisk probably the report output something like this:

RiskREF DeptRef RiskDesc ReviewFreq DeptRef Process Rest Of Fields>>>>>>>>>
2 1 Fall from height. 12 10 Cleaning windows first floor.

Risk Amendments:
Date 28/05/2005 Cleaning windows first & second floor.
10/01/2005 Danger of falling.


Next assessment etc................
etc.........

Thanks in advance.
Tim

 

[Darrylles]

Hi Tiger,

You are trying to write a query, but you haven't said what your problem is in writing it. What is it?

You have shown the table definitions, but what are the related fields? There is no obvious FK (foreign key) in tblRisk.

You also mention 'changed fields'. Do you mean 'new additions' to tblRisk or 'changed fields' in tblRisk? If the latter, what indicates what has changed in tblRisk?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[tigersden]

Hi Darrylles,
There is no foreign key in tblRisk as you correctly mentioned, the foreign key is in tblAuditRisk (RecordPKey) which references RiskRef in tblRisk.
Sorry I was not clearer in my explanation.
This what I am having problems with, get all fields form tblRisk then the modified data for these fields from tblAuditRisk. As well as getting the first OrigValue(tblAuditRisk) then each subsequent NewValue(tblAuditRisk)for each field whose data has changed, the field name of the changed data is stired in tblAuditRisk.
Hopes this makes things clearer.