By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,201 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Update Null Fields in Table 1 from Table 2 & 3

P: 36
I am trying to update a table that is Inner Joined or related to 2 other tables with similiar data. For Instance:

Table 1
-----------
A - B
--------
y - x
z - null

Table 2
-----------
A - B
----------
y - Null
z - x

AB are the Field Names
A from Table 1 is in a one-to-many relationship to A in table 2
Where B is Null in table 1, Table 2 would have data for it.
I just want to update the X over when it is null.

I've tried:
IIF(isnull([table1].[b],([table2].[b])
and that does not yield the correct results.
Aug 20 '08 #1
Share this Question
Share on Google+
4 Replies


100+
P: 167
It seems perfect for using Update query.

Make an update query based on Table1 and Table2 where these two are joined through Primary/foreign key.
From Table1 field B update to Table2.B
In where row put: IsNotNull

This way only values that are not null will be copied to Table1.

After that make a change in a query updating Table2 with Table1.B
where ..IsNotNull

That way Table2 will be also updated, and null values will be eliminated from both tables.

Note:
before running the query (and updating the table) make sure to click 'select button' on the toolbar in order to preview what will be updated!
Aug 20 '08 #2

NeoPa
Expert Mod 15k+
P: 31,660
You say table 1 to table 2 is a One-to-Many join, yet you don't appear to have considered the situation where [Table 1].A is Null and there are multiple matching records in [Table 2] with various different values for B.

What will happen is that you will be left with the last one to be applied. Not likely to be what you require.
Aug 20 '08 #3

P: 36
Fortunately. [table1].[A] will never be null.

I will try the update query again. I've tried it before and it did not function correctly. I will post results here.

Thank You.
Aug 21 '08 #4

NeoPa
Expert Mod 15k+
P: 31,660
My mistake.

I meant to say :
You say table 1 to table 2 is a One-to-Many join, yet you don't appear to have considered the situation where [Table 1].B is Null and there are multiple matching records in [Table 2] with various different values for B.

What will happen is that you will be left with the last one to be applied. Not likely to be what you require.
Aug 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.