Comparison Query?

  • Thread starter Thread starter Don
  • Start date Start date
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
 
Create a query that joins the two tables, then compare each of the fields in
question.

SELECT Table1.ID, Table1.Field1, Table2.Field1
, Table1.Field2, Table2.Field2
, Table1.Field3, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table1.Field1 <> Table2.Field1
OR Table1.Field2 <> Table2.Field2
OR Table1.Field3 <> Table2.Field3

If there's a chance that any of the fields may be Null, use the Nz function
to convert the Nulls to something that can be compared (Null <> Null)

SELECT Table1.ID, Table1.Field1, Table2.Field1
, Table1.Field2, Table2.Field2
, Table1.Field3, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Nz(Table1.Field1, 0) <> Nz(Table2.Field1, 0)
OR Nz(Table1.Field2, "") <> Nz(Table2.Field2, "")
OR Nz(Table1.Field3, 0) <> Nz(Table2.Field3, 0)

Note how I'm forcing Nz to return a number or a string, depending on what
the underlying data type is.
 
Thanks Doug,
I appreciate the info. The only problem is htat the tables I am dealing
with have thousands of records. What I am trying to do is have the query
return just those records that have had any of the field values altered.
Thus if my original table of 5000 records, had 136 records altered, I could
have the query give me the 136 records that have changed from the "before"
table, to the "after" table. Hope this makes sense. Thanks Again! Don
 
Doug, I think I see what you're doing. Please forgive me as I'm not well
versed in SQL, so this seems a little foreign to me. When you said compare
each of the fields, I thought you meant me physically comparing each & every
field, which would definitely be time consuming. Apparently there's not a
way using the standard Access grid. If not, Iguess I'd better learn SQL.
Thanks again. Don. Also apologise for cross post. Didn't realise it was a
problem. Take care.
 
Don said:
Doug, I think I see what you're doing. Please forgive me as I'm not well
versed in SQL, so this seems a little foreign to me. When you said compare
each of the fields, I thought you meant me physically comparing each & every
field, which would definitely be time consuming. Apparently there's not a
way using the standard Access grid. If not, Iguess I'd better learn SQL.
Thanks again. Don. Also apologise for cross post. Didn't realise it was a
problem. Take care.
Click to expand...

Don, what Doug gave you can be built in the query design grid. We use SQL in
the Newsgroups because there is no way to show an example of the GUI grid here.
In the Query Design Grid you would just build the criteria making sure that each
field comparison is on its on criteria row so they result in an OR expression
instead of an AND expression.
 
Back
Top