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.f1<>B.f1, -1)
OR
Nz(A.f2<>B.f2, -1)
OR
Nz(A.f3<>B.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...
