compare table before and after update query and output the changes

[clcnewtoaccess]

I have a DB that tracks work in our building. The dates that items are due
can change due to production requirements or scheduling. I have an update
query that will update the dates but I would like to know which ones changed
so I can alert the managers. Right now I have to output an excel spreadsheet
before and after the update and do a manual comparison. Is there a way to do
a comparison before and after the query runs that will output a report on the
differences?

 

[Golfinray]

You could run an unmatched query or find duplicates query NOW and see what
you have then update and compare the two. The query will show you so you
don't have to do it manually.

 

[Ken Sheridan]

The criteria applied to the UPDATE query will determine which rows are
updated, so the same criteria can be applied to a SELECT query. If this is
opened before the UPDATE query is executed then it will return those rows
which will be updated. You can base a report on this query and print it out
before executing the UPDATE query. You might also be able to print it out
after executing the UPDATE query to show the changes, but this depends on
whether the criteria relate to the column(s) updated or are independent of
them.

While this should be easier than manually comparing two Excel worksheets you
might be better off considering amending the logical model by decomposing the
relevant table so that the dates are stored in a related table, into which a
new row is inserted when changes are made rather than updating the date
values, and thus losing the values before the updates. You'd execute an
'append' query rather than an 'update' query. In effect you'd be creating an
audit trail of the amendments. DateTimeStamping the rows in the related
table, by having a column with a DefaultValue property of Now(), would enable
you to group the amendments on this column, those with the latest (MAX)
DateTimeStamp overall being those amended when the 'append' query was last
executed, and those with the latest (MAX) DateTimeStamp per WorkItem (or
whatever) containing the current due dates (I'm assuming that the due dates
might be amended forward or backwards so you can't rely on the MAX due date
per work item being the current one).

Ken Sheridan
Stafford, England