Searching for changes in large groups of records

[max6166]

Everyday, I receive a dump of about 20,000 records keyed by an account
number. There are about 20 fields in the table.

Each day, I need to determine which account records have changed, and
append any changed records to an historical log. The log has the exact
same table structure as the incoming table, with the exception that it
adds a date stamp. Typically, only about 10 records change each day.

I have managed to find a few methods to do this task, but they are all
*extremely* slow. Most of my methods consist of looping through the
new incoming data on record at a time, and then looking up the last
stored record for the given account by using either a query or another
loop.

I have a feeling there is a much better way to approach the problem,
but am coming up blank. Does anyone have any suggestions or advice for
doing this task in a more efficient manner?

 

[John Spencer]

Are you saving the entire record or just the changes?

Is Account Number unique (Primary Key) in the source file?
Do you have at least on record for each Account Number in the Target File?
Do all fields have a value or are the fields sometimes null

ASSUMING that there is at least one of every account number in the Archive
table you can do something like the following.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Candidate records:
SELECT A.*
FROM ArchiveTable as A INNER JOIN
(SELECT AccountNumber, Max(DateStamp) as LastUpdate
FROM ArchiveTable) as B
ON A.AccountNumber = B.AccountNumber
AND A.DateStamp = B.DateStamp


INSERT INTO ArchiveTable (AccountNumber, DateStamp, Field2, field3, ..., Field20)
SELECT S.AccountNumber, Date() as TheUpdateDate, S.Field2, S.Field3, ...,
S.Field20
FROM SourceTable AS S INNER JOIN CandidateRecords As A
ON S.AccountNumber = S.AccountNumber
WHERE A.LastUpdate <> Date()
AND (
(S.Field10 <> A.Field10
Or S.Field10 is Null and A.Field10 is not Null
OR S.Field10 is Not Null and A.Field10 is Null)

OR NZ(S.FieldDate,"#1/1/1900#") <> Nz(A.FieldDate,#1/1/1900#")
)

You will need to add other criteria or break the process down into multiple
steps where you check groups of fields at a time.

You can add any AccountNumbers in that aren't already in the Archive table by
using
INSERT INTO Archive (List all fields plus Archive datetime field)
SELECT <List all Fields in Source table>, Date()
FROM SourceTable as S LEFT JOIN Archive as A
ON S.AccountNumber = A.AccountNumber
WHERE A.AccountNumber is NULL

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[max6166]

ASSUMING that there is at least one of every account number in the Archive
table you can do something like the following.

Candidate records:
SELECT A.*
FROM ArchiveTable as A INNER JOIN
   (SELECT AccountNumber, Max(DateStamp) as LastUpdate
    FROM ArchiveTable) as B
ON A.AccountNumber = B.AccountNumber
AND A.DateStamp = B.DateStamp

INSERT INTO ArchiveTable (AccountNumber, DateStamp, Field2, field3, ..., Field20)
SELECT S.AccountNumber, Date() as TheUpdateDate, S.Field2, S.Field3, ...,
S.Field20
FROM SourceTable AS S INNER JOIN CandidateRecords As A
ON S.AccountNumber = S.AccountNumber
WHERE A.LastUpdate <> Date()
       AND (
       (S.Field10 <> A.Field10
       Or S.Field10 is Null and A.Field10 is not Null
       OR S.Field10 is Not Null and A.Field10 is Null)

        OR NZ(S.FieldDate,"#1/1/1900#") <> Nz(A.FieldDate,#1/1/1900#")
       )

You will need to add other criteria or break the process down into multiple
steps where you check groups of fields at a time.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you so much, John! Your solution worked almost exactly "as is"
with my data and seems to be lightning fast so far.

And thank you for taking the time to write such a well thought out
reply as well.