Maintaining a Log of Changes Made to a Record During Import

[Mac]

I'm ready to lose my mind with this one. Searches using proper terms
and common terms are coming up empty on this. I really hope someone
can clue me in.

Here's the problem. I have a database that ties together data from
several different sources. Because the data pull from each source
contains thousands of records, they are imported directly from the
files that we receive from each source. It's become increasingly
necessary to track the complete change history of each record in each
of the tables. The end result is that I can pull up something like the
following:

EquipmentID DateImported Status Comments
10119 10MAR09 Green n/a
10119 11MAR09 Yellow potential problem, might
fail soon
10119 12MAR09 Red equipment has failed

If we were entering/editing the data ourselves this wouldn't be a
problem. There are a wealth of algorithms to handle that situation. I
need to come up with a strategy to compare old-record/new-record
changes, per field, each day, during an automated import of thousands
of records for each table.

Every solution I come up with seems arduous, clunky and worst of all
due to record count, SLOW. I remember enough from my CS course to
remember that theres a very efficient way to compare whole records,
but I can't remember what it is.

Can someone give me a clue here? Or is this problem so far out of the
norm that I may have to settle for my clunky solution?

Thanks for any advice!
-Mac

 

[Tom Wickerath]

Hi Mac,

I remember enough from my CS course to
remember that theres a very efficient way to compare whole records,
but I can't remember what it is.

Perhaps you are thinking about the SQL DistinctRow predicate? Here is a link
to a recent paper on this topic:

Difference Between "Unique Values" (SELECT DISTINCT) and "Unique Records"
(SELECT DISTINCTROW) in Microsoft Access Queries

http://www.fmsinc.com/MicrosoftAccess/query/distinct_vs_distinctrow/unique_values_records.asp


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________