[AccessD] Audit Record Changes

Sorry I am a bit late on posting (weather and DSL have been causing me to
fall behind a bit).

I know you were given a solution to this issue, but it uses a temp table.

Here is an SQL method of producing the same results.  (I've been on an SQL
kick lately...hehehehe...however, this prevents having to create temp tables
(and possibly write over or destroy the same).  I personally shy away from
creating temp tables, due to their nature of bloating a database......there
are other concerns too....)

Anyhow, I built a table called tblTest, and put EmpID, Name, Address, Salary
and DateChanged as fields.  I then built this SQL Statement:

SELECT "Name" AS FieldName, (SELECT TOP 1 tblTest.Name FROM tblTest WHERE
tblTest.DateChanged<T1.DateChanged) AS OldValue, T1.Name AS NewValue,
T1.DateChanged
FROM tblTest AS T1
WHERE (T1.Name <> 
(SELECT TOP 1 tblTest.Name
FROM tblTest
WHERE tblTest.DateChanged<T1.DateChanged
ORDER BY DateChanged DESC) AND T1.DateChanged <>
(SELECT Min(DateChanged) AS MinOfDateChanged
FROM tblTest))
ORDER BY DateChanged;

UNION SELECT "Address" AS FieldName, (SELECT TOP 1 tblTest.Address FROM
tblTest WHERE tblTest.DateChanged<T1.DateChanged) AS OldValue, T1.Address AS
NewValue, T1.DateChanged
FROM tblTest AS T1
WHERE (T1.Address <> 
(SELECT TOP 1 tblTest.Address
FROM tblTest
WHERE tblTest.DateChanged<T1.DateChanged
ORDER BY DateChanged DESC) AND T1.DateChanged <> 
(SELECT Min(DateChanged) AS MinOfDateChanged
FROM tblTest))
ORDER BY DateChanged;

UNION SELECT "Salary" AS FieldName, (SELECT TOP 1 tblTest.Salary FROM
tblTest WHERE tblTest.DateChanged<T1.DateChanged) AS OldValue, T1.Salary AS
NewValue, T1.DateChanged
FROM tblTest AS T1
WHERE (T1.Salary <> 
(SELECT TOP 1 tblTest.Salary
FROM tblTest
WHERE tblTest.DateChanged<T1.DateChanged
ORDER BY DateChanged DESC) AND T1.DateChanged <> 
(SELECT Min(DateChanged) AS MinOfDateChanged
FROM tblTest))
ORDER BY DateChanged;

If you notice, each 'union' select statement is an identical match to the
first SQL statement, with the following exceptions.  First, After the SELECT
statement, the "xxxxx" AS FieldName represents the field you are looking at.
Then, I replace the reference of that field throughout the SQL for that
Union statement.  (Thus it's pretty easy to add fields to be
searched/compared on.  Just copy and paste one segment, rename the FieldName
field, and then replace the 'comparing' field 4 places.  

To explain how this query works, first, look at just one union segment,
let's take the first one (that doesn't have a Union statement in the front.
It is a query that has 4 fields.  The FieldName is represented as the text
value of "Name".  That lets us know what field we are seeing changes too.
The next field is actually a subquery, which shows us the value of the
'previous record', which produces, in the subquery, the record before (by
date) the current record of the main query.  Then there are two 'criteria'
which must be met in the third field (which is the current value of the
field.).  The first criteria says that the Name field is NOT Equal to the
value returned by the subquery (which is pulling the previous record by
date).  The next criteria is that the date is NOT the minimum date.
(FYI....This works with the data you presented.  If you have multiple
Employees, the SQL above WILL NOT work.  Because it is going to pull all
records up.  YOu will need to change both subquerries to require the
matching ID key.  (I don't know if you would want that as the EmpID, or an
Autonumber from another table...a 'master' employee ID).  If you send me a
sample of your actual table, I'd be glad to show you how to fix the SQL to
match.  That last criteria prevents the query from showing 'changes' for the
first entry.  (Since the subquery pulls nothing up...it would meet the not
equals requirement for the field matching.

Finally, that SQL statement is tripled, with a UNION Statement before the
2nd and 3rd parts, and with the proper modifications to 'check' each field.
The Union query allows the results from each 'field' checking query to be
lumped into one outgoing recordset.

Have fun....

Drew

-----Original Message-----
From: gregg steinbrenner [mailto:greggs at msn.com]
Sent: Tuesday, February 25, 2003 3:29 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Audit Record Changes


Every time someone makes a change I keep a copy of the old record in a clone
table.  An example table would look as follows

EmpID Name		Address  		Salary  	DateChanged
102	Gregg		123 Somewhere	50000		1/1/03
102	Gregg		111 Elsewhere	50000		2/2/03
102	Greggs	111 Elsewhere 	55000		3/3/03

I would like to create a query (if possible) or code that would create a
second table that shows me what field, changed from what, to what, on what
day as follows...

Field		Old Value		New Value		DateChanged
Address	123 Somewhere	111 Elsewhere	2/2/03
Name		Gregg			Greggs		3/3/03
Salary	50000			55000			3/3/03

Any ideas?

Is it possible to do this without listing every field name in the code?

Humbly yours,

Gregg Steinbrenner

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com