Highlight data changes

[rightcoast]

Hoping to get some advice/brilliant ideas on how to highlight data
changes in a report. The report will be in Excel from data stored in
Access 2003. Some background - the data is currently stored in Excel,
but will be migrated to Access (front end/back end) for all the usual
reasons. Currently, data is entered via userforms and there are two
hidden worksheets, one which holds the most current data, and another
which holds the prior version of the data (the prior data is refreshed
periodically when the user clicks a button). If the current data does
not match the prior data, the font is red. When the client report is
created (in a separate file), the font color is retained so any
changes are easily seen.

I'm not sure how best to replicate this in Access. Highlighting any
changes is very important to the client, so this feature must be
retained. I've seen several posts about tracking data changes and I
can think of any number of ways to accomplish that, but the issue is
comparing old data to new so as to flag differences - I can think of a
way to do that such that performance wouldn't be poor. Opening two
recordsets and comparing each field in each record for 20+ fields in
approximately 500 records would likely be quite slow.

In a perfect world, it would be great to have some sort of record/
field level flag that could be set when data is changed, so that if
true, change the font color. I have searched the newsgroups but not
yet found any solutions. Any help will be most appreciated.

 

[Lance]

I see a couple ways of doing this.

1) Include a "date/time changed" field in your table, and have your report
highlight based on records recently changed.

2) Store an "old copy" of your data to a 2nd table, then compare your
current table up against it. You shouldn't have to do a line by line and
field by field comparison, all you need to do is link the two nearly
identical tables and compare changes in the matched records.. assuming your
data has a key ( if not make one! )

I would lean towards option #1.

 

[rightcoast]

Thanks for your response. Option 1 won't do the trick as I need to
highlight only what's changed in the record, not the entire record
itself - i.e., if only 3 of 20 fields in the record changed, then only
those three fields would be in red, the rest in black. The data comes
from a query linking 4 tables (all have unique keys, changes will
happen in 2 of the 4 tables, in rare cases 3 of 4) - even if I do a
query on the 'old copies' of the tables, how would I determine what
has changed other than doing a row by row, field by field comparison??

 

[Lance]

OK. First make a query linking your 4 current tables and then make a query
linking your 4 "old" tables.

Then make a 3rd query, linking the first 2 queries ( you need to make sure
each row uniquely links to the matching row in the other ). Include ALL
non-key data fields ( plus whatever key fields you want ).

Make a report based on this 3rd query, but only display the fields from the
current tables. On each field set the conditional formatting to be something
to the effect of if currentField1 not equal oldfield1.. <apply formatting>.

 

[rightcoast]

I can see that this would be a good solution if the report were being
created within Access, but the data is exported to Excel (cannot
change that), and conditional formatting is not preserved when
exporting data. Still keep coming back to the inefficient method of
row/field comparison...any other thoughts?

 

[Larry Daugherty]

You are trying to solve a business issue that is presently entirely
within Excel. Solve it there. Within Excel it's eminently doable.
You already have the idea for a workable algorithm and you'll be
surprised at just how little time it takes to perform the calculations
in Excel.

When you actually implement things in Access then apply the Access
ideas that you've been given.

HTH