Please help with Comparing tables!!!

I am in a pickle!! I have two tables that I update. However, I did not
update a couple of fields. Now I have to go back and compare table B against
table A and find any data that is not identical and then update with data
from table B. The tables contain over 3000 rows. Can this be done easily
with a query? Thank you.

"TotallyConfused" wrote:
I am in a pickle!! I have two tables that I update. However, I did not
update a couple of fields. Now I have to go back and compare table B
against
table A and find any data that is not identical and then update with data
from table B. The tables contain over 3000 rows. Can this be done easily
with a query? Thank you.

I imagine your query will look something like:

UPDATE
tblA
INNER JOIN
tblB
ON tblA.PK = tblB.PK
SET
tblA.f1 = tblB.f1,
tblA.f2 = tblB.f2
WHERE
tblA.f1 tblB.f1
OR
tblA.f2 tblB.f2;

assuming you have a primary key (PK) to match
in both tables...

and none of your fields (f1, f2,..) are NULL, i.e., the
WHERE clause as written will not find where one or
the other field is NULL but they are not equal.

One workaround for NULL's problem is to choose
a value that the fields will never be (like "!#$%^&")
and use null-to-zero function...

WHERE
NZ(tblA.f1,"!#$%^&") NZ(tblB.f1,"!#$%^&")
OR
NZ(tblA.f2,"!#$%^&") NZ(tblB.f2,"!#$%^&");

Thank you for responding. Both my tables have a PK. Does this mean I have
to update the table first with something so that there are no Nulls before
comparing?

"Gary Walter" wrote:


"TotallyConfused" wrote:
I am in a pickle!! I have two tables that I update. However, I did not
update a couple of fields. Now I have to go back and compare table B
against
table A and find any data that is not identical and then update with data
from table B. The tables contain over 3000 rows. Can this be done easily
with a query? Thank you.

I imagine your query will look something like:

UPDATE
tblA
INNER JOIN
tblB
ON tblA.PK = tblB.PK
SET
tblA.f1 = tblB.f1,
tblA.f2 = tblB.f2
WHERE
tblA.f1 tblB.f1
OR
tblA.f2 tblB.f2;

assuming you have a primary key (PK) to match
in both tables...

and none of your fields (f1, f2,..) are NULL, i.e., the
WHERE clause as written will not find where one or
the other field is NULL but they are not equal.

One workaround for NULL's problem is to choose
a value that the fields will never be (like "!#$%^&")
and use null-to-zero function...

WHERE
NZ(tblA.f1,"!#$%^&") NZ(tblB.f1,"!#$%^&")
OR
NZ(tblA.f2,"!#$%^&") NZ(tblB.f2,"!#$%^&");

Could you please write this how I should put this on qry grid? This SQL is
not working. I get the following message " Can't run the macro or callback
function fDesign make sure the macro or function exists and take the correct
parameters.

"Gary Walter" wrote:


"TotallyConfused" wrote:
I am in a pickle!! I have two tables that I update. However, I did not
update a couple of fields. Now I have to go back and compare table B
against
table A and find any data that is not identical and then update with data
from table B. The tables contain over 3000 rows. Can this be done easily
with a query? Thank you.

I imagine your query will look something like:

UPDATE
tblA
INNER JOIN
tblB
ON tblA.PK = tblB.PK
SET
tblA.f1 = tblB.f1,
tblA.f2 = tblB.f2
WHERE
tblA.f1 tblB.f1
OR
tblA.f2 tblB.f2;

assuming you have a primary key (PK) to match
in both tables...

and none of your fields (f1, f2,..) are NULL, i.e., the
WHERE clause as written will not find where one or
the other field is NULL but they are not equal.

One workaround for NULL's problem is to choose
a value that the fields will never be (like "!#$%^&")
and use null-to-zero function...

WHERE
NZ(tblA.f1,"!#$%^&") NZ(tblB.f1,"!#$%^&")
OR
NZ(tblA.f2,"!#$%^&") NZ(tblB.f2,"!#$%^&");

Always make sure you have a backup
or try first on a copy of your data...

In Database Window that shows "Objects"
in left pane, click on "Queries."

In right pane, click on "Create Query in Design View"

In the Show Table dialog box,
click on your table A,
click Add,
click on your table B
click on Add,
and then click Close.

You should now show your 2 tables
in the query designer.

Right-mouse click on the "A" table
and choose Properties.
In the Alias row, type in
A
then close the Properties dialog box.

Right-mouse click on the "B" table
and choose Properties.
In the Alias row, type in
B
then close the Properties dialog box.

I believe you have a PK field in each
table that when we join them, the correct
record in A will "line up" with correct
record in B.

Click and hold down on A's PK field
and "drag and drop"
over on B's PK field.

You should now have a (join) line
connecting the 2 tables going
from A.PK to B.PK fields.

If I understood correctly, you want
to update some fields in table A
from corresponding fields in table B
(when they are joined on PK).

Double-click on those fields in A
that you want to update to send them
down to the grid.

For now, send corresponding fields
from B also to grid. Run select query
to see what you have.

Go back to grid and try setting Criteria
to return only where the fields are not
equal (want to OR so Criteria will be
stair-stepped down across grid)

Field: f1 f2 f1 f2
Table: A A B B
Sort:
Show: x x x x
Criteria:
Or: A.f1
Or: A.f2

When you run this query, does it show
all the records you want to update?

One reason it might not is because
one or more fields in A (orB) is null.
One workaround was to wrap in NZ
supplying an alternative that will never
exist in your data

Field: f1 f2 NZ(B.f1,"zzzz") NZ(B.f2,"zzzz")
Table: A A
Sort:
Show: x x x x
Criteria: NZ(A.f1,"zzzz")
Or: NZ(A.f2,"zzzz")

This query should now show all the fields that
need updated (where a field in A is different
from its corresponding field in B).

Now change this select query to an
update query by clicking in top menu
on "Query/Update Query"

The field rows in the grid will change and all
you need to do is fill in the "Update To:" row
under table A fields in grid:

Field: f1 f2 NZ(B.f1,"zzzz") NZ(B.f2,"zzzz")
Table: A A
Update To: B.f1 B.f2
Criteria: NZ(A.f1,"zzzz")
Or: NZ(A.f2,"zzzz")

If you had more than 2 fields, don't forget to "stair-step"
your Criteria so they "OR" the equalities. If they are all
on one Criteria line, they will be ANDed. That would mean
*all* the fields would have to be different for the record
to be updated. So...a third and fourth field might look like:

Field: NZ(B.f3,"zzzz") NZ(B.f4,"zzzz")
Table:
Update To:
Criteria:
Or:
Or: NZ(A.f3,"zzzz")
Or: NZ(A.f4,"zzzz")

If this still does not work for you, please go to SQL View,
copy the text there, and paste back here in a post.

BTW, a better method for handling Nulls when you are
filtering for *no match* is


WHERE
Nz(A.f1B.f1, -1)
OR
Nz(A.f2B.f2, -1)
OR
Nz(A.f3B.f3, -1)

which is a lot easier to write out in SQL View
than use the grid. If either field is Null, that record
will be returned; plus, you don't have to hope your
data will never be some value like "zzzz" and screw
up the logic when using the other method...


"TotallyConfused" wrote:
Could you please write this how I should put this on qry grid? This SQL
is
not working. I get the following message " Can't run the macro or
callback
function fDesign make sure the macro or function exists and take the
correct
parameters.

"Gary Walter" wrote:


"TotallyConfused" wrote:
I am in a pickle!! I have two tables that I update. However, I did not
update a couple of fields. Now I have to go back and compare table B
against
table A and find any data that is not identical and then update with
data
from table B. The tables contain over 3000 rows. Can this be done
easily
with a query? Thank you.

I imagine your query will look something like:

UPDATE
tblA
INNER JOIN
tblB
ON tblA.PK = tblB.PK
SET
tblA.f1 = tblB.f1,
tblA.f2 = tblB.f2
WHERE
tblA.f1 tblB.f1
OR
tblA.f2 tblB.f2;

assuming you have a primary key (PK) to match
in both tables...

and none of your fields (f1, f2,..) are NULL, i.e., the
WHERE clause as written will not find where one or
the other field is NULL but they are not equal.

One workaround for NULL's problem is to choose
a value that the fields will never be (like "!#$%^&")
and use null-to-zero function...

WHERE
NZ(tblA.f1,"!#$%^&") NZ(tblB.f1,"!#$%^&")
OR
NZ(tblA.f2,"!#$%^&") NZ(tblB.f2,"!#$%^&");