Update certain record fields in one table if data exists in anothe

[tcanis]

I need know how to update one table with null values in various fields using
another table imported from another program. Both tables are tied together
by a unique ID number. "tblUPLOADservnum" is imported into access from
another program. It is a temporary table, once the data from this table is
uploaded to the main table, the table is deleted. tblSBSservnumGM, is the
main table.

Some of the fields in certain records in the main table wasnt imported
initially when the record was created so now I need to update the existing
records where the value of the field is currenlty null. The imported table
generally has this information but not always. Sometimes the data for a
specific field is just plain not available. Can an update query be the way
to go? If so how do I set this up.

This is my goal: Compare the data of the imported table to the main table.
If any of the records fields in the main table are null and the imported
table has data for that record for those fields only then I want the main
table updated with the information from the imported table. There are 7
fields I am interested in, line#, Service#, Prod Finish#, Eng#/LF#, Color,
Model Year, and comments

 

[vanderghast]

It is not clear which field(s) are to be used to make a 'match' between the
two tables.

Make a new query, an update query. Bring the two tables, in the upper part,
and JOIN the field(s) which are required to make a match.

For each of the other fields, make a new column in the grid, add the
expression:

Nz( [TableToBeUpdated].[fieldName],
[TableWithUpdatingValues].[FieldName] )

which is to update [TableToBeUpdated], field [fieldName].


The SQL view should be like:

UPDATE tableToBeUpdated INNER JOIN tableUpdating ON ... {fields making a
match} ...
SET [TableToBeUpdated].[FieldName] = Nz( [TableToBeUpdated].[fieldName],
[TableWithUpdatingValues].[FieldName] ),
... { repeat for each field to be updated }



it is the Nz which makes all the job. Indeed, if
[TableToBeUpdated].[fieldName] is NOT null, Nz returns it, else, it returns
[TableWithUpdatingValues].[FieldName]. So, in the end, you field is either
updated by itself, either (if null) updated by the value in the other table.


Note that each and every records will be updated (even if they are updated
by their 'old' values, they are technically updated none the less).



Vanderghast, Access MVP