Find differences query

[exo]

I have a query I use across 20 tables to check for differences for them.

Before my updating process begins my original table is copied to a backup table.

In this case the original table is named "vermillion" and it is copied to "vermillion_b4" as a backup.

Generally I then run updates on the original table, and at the end I run a make table query that checks each record in vermillion for a change against vermillion_b4, which ideally creates a table that only contains the differences (ie: the changes that were made that day, whether they be additions or updates).

This works flawless on 19 of the tables, but not on Vermillion for some reason. Out of over 10,000 records the query ALWAYS returns at least 464 of the records as being changed.

I can take the "vermillion" table, copy it directly to "vermillion_b4", make no changes at all and run the query, and I still get the same 464 records. I don't see how an exact duplicate of itself can return any changes on this table, but not any of the others.

here is the query I am using:

SELECT Vermillion.* INTO Verm_Updated
FROM Vermillion LEFT JOIN Vermillion_B4 ON Vermillion.UWI=Vermillion_B4.UWI
WHERE (((Nz(Vermillion.Operator,""))<>Nz(Vermillion_B4.Operator,"")) And ((Vermillion.Flag) Is Null)) Or (((Nz(Vermillion.[X Coord],""))<>Nz(Vermillion_B4.[X Coord],"")) And ((Vermillion.Flag) Is Null)) Or (((Nz(Vermillion.[Y Coord],""))<>Nz(Vermillion_B4.[Y Coord],"")) And ((Vermillion.Flag) Is Null)) Or (((Nz(Vermillion.Status,""))<>Nz(Vermillion_B4.Status,"")) And ((Vermillion.Flag) Is Null))

(note: my original query does not have spaced in the word 'operator' and 'status'. When i have my post open for editing I do not see these spaces either, but for some reason after I post it is adding these arbitrary spaces)

The query continues on a bit longer with some more of the same. I can post the whole thing if necessary, but the format is exactly the same.

I use this exact query for my other tables - I simply copy this code to wordpad, use a text replace to change the table names, and run it directly. I even tried replacing this query with one that works and just changed the table names to match the vermillion tables. Same results.

How can a tables fields be different from an exact duplicate of that table?

Thank you very much to anyone who can help me. this is driving me nuts.

 

[wiklendt]

the obvious question here is - why? why are you doing this convoluted, hard, round-about table making/appending thing??

 

[gemma-the-husky]

you may get a difference between a blank (zls) and a null value - although you wouldnt see anything - could it be that

the other possibility is that you have "duplicated" records (ie the key you are using is duplicated)

so the first query shows some records as incorrect - but when they get updated - it means that other records with the same key are now wrong - so these now show as different.

 

[exo]

the obvious question here is - why? why are you doing this convoluted, hard, round-about table making/appending thing??

Because I am not aware of another way to make a difference table. It is very important for this process that I know exactly which records have changed or been added.

The way I came up with doing this is comparing the updated table to the original table, using the method described above. If there is another way, then i would be happy to hear about it.

 

[exo]

you may get a difference between a blank (zls) and a null value - although you wouldnt see anything - could it be that

the other possibility is that you have "duplicated" records (ie the key you are using is duplicated)

so the first query shows some records as incorrect - but when they get updated - it means that other records with the same key are now wrong - so these now show as different.

It looks like you were on to something. There are duplicates that should not be there, and the update process has only been updating the first instance. This means that it sees itself twice in the original table, and of course it is different than one of the instances. The primary issue here is how these duplicates could have gotten here in the first place.

Thank you for the great suggestion!

 

[wiklendt]

It is very important for this process that I know exactly which records have changed or been added.
...

If there is another way, then i would be happy to hear about it.

There are many more ways to add auditing to tables. one method i use by adding two fields to my tables: DateCreated and DateModified.

in DateCreated, i make the default value as

=Now()

whenever a new record is created, it will automatically input the exact date and time that it was created.

in DateModified, you need to add code to your data entry forms to add the date/time to that field whenever changes are saved (this can happen automatically, but some people use unbound forms and have to code extra to save what access normally saves automatically)

if you then need to search which records were added or modified, you can make a simple form where you add the date in question (e.g., today's date) and press a button that invokes a query showing which records were added/modified on/since that date.

if you're clever you can make this work on all tables/queries you want with just the one form (rather than manually copying/modifying/pasting the SQL in notepad... what a drag!)

there are many many more ways to audit/track in access, you'll just have to search the forums and decide which is best for you. "audit" is the keyword you're after.