Comparison Query?

D

Don

I know you can run a "find unmatched query" that creates a query that finds
records in one table that have no related records in another table, but what
about a query that can find records where the field values have changed or
are different. For example, say I have two identical tables with ten
records, and the values of the primary key of the ten records are the same
between the two tables, but some of the other field values are different
(for example 4 records). How can I run a query to find those four records? I
am trying to compare a table that is constantly updated, with the same table
as it was say a month ago, or year ago. Hopefully I didn't sound too
confusing. Any help is greatly appreciated. Thanks, Don
 
C

CSmith

Hi,
I am trying to compare a table
that is constantly updated, with
the same table as it was say a
month ago, or year ago.
Click to expand...

You can achieve these results by having a left join from the current table
to the vintage table, then relate on the common columns of interest, and use
Is Null as the criteria for the columns in the vintage table.
 
J

John Spencer (MVP)

The query would look something like the following for ONE field.

SELECT TableA.Field1, TableB.Field1
FROM TableA INNER JOIN TableB
ON TableA.PrimaryKey = TableB.PrimaryKey
WHERE TableA.Field1 <> TableB.Field1
OR (TableA.Field1 Is Null AND TableB.Field1 Is Not Null)
OR (TableA.Field1 is Not Null AND TableB.Field1 is Null)

To do this for multiple fields you might want to use the NZ function instead of
the complicated query criteria.

Something like the following in the Query Grid.
Field: Nz(Table1.Field1)
Criteria: <> Nz(Table2.Field1)

Field: Nz(Table1.Field2)
Criteria:
Criteria (Line2) <> Nz(Table2.Field2)

Etc.

The only other way would be to use field to store the date and time of the last
modification to the record. Then you could compare that date and time.
 
C

CSmith

That's WAY TOO MUCH CODE OVERHEAD for something that a left join and an Is
Null criteria combination can simply handle, as I wrote earlier. And your
example is just for ONE column. Building a large (and much slower) Where
clause and inner join for this objective would not be the most elegant
approach. The database can trim records itself using the power of the join.
 
J

John Spencer (MVP)

Whoops! I misread your suggestion. I thought you were only linking on the
primary key between the two tables.
 
C

CSmith

Of course, there's more than one way to skin a salmon, but we have to keep
that end-user in mind. ;-)
 
Top