update query

[Karen]

Hi,

I was wondering if there was a way to update several fields in a table using
one update query. Basically, I would like to update 10 fields in tblA to the
corresponding value in tblB if the value in table B is null.
Using the query design windown for each field I want to update I have
(example)
[hip_measure]
tblA
[tblB].[hip_measure]
is null

Since I have the criteria "is null" about 10 times no records match my
criteria. Is there a way to do this all in one query? If not I guess I could
write 10 little update queries and have a macro that runs them?
Thanks.

 

[Jeff Boyce]

Karen

Are you saying that if ANY of the ten fields holds a Null in tblB, you want
tblA's values for those 10 fields to be updated to the values in tblB?

First, why? ... as in "why do you have the same 10 fields in two different
tables?"

If you place the "Is Null" criterion for each field on separate criterion
rows, Access treats it as "OR" -- but if the "Is Null" is all on the same
row, Access treats the selection criteria as AND's.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Perhaps what you want is something like the following which will update
field in table a that are null to the value in tableB. If a Field is not
null it gets updated to itself.

UPDATE TableA INNER JOIN TableB
On TableA.SomeField = TableB.Somefield
SET TableA.FieldA = NZ(TableA.FieldA,TableB.FieldA)
, TableA.FieldB = NZ(TableA.FieldB,TableB.FieldB)
, TableA.FieldC = NZ(TableA.FieldC,TableB.FieldC)
WHERE TableA.FieldA is Null Or TablA.FieldB is Null Or TableA.FieldC is Null
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..