473,320 Members | 1,988 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update Null Fields in Table 1 from Table 2 & 3

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
4 2066
hjozinovic
167 100+
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

5
by: S.Patten | last post by:
Hi, I have a problem with updating a datetime column, When I try to change the Column from VB I get "Incorrect syntax near '942'" returned from '942' is the unique key column value ...
7
by: Justin | last post by:
I am extremely new at SQL Server2000 and t-sql and I'm looking to create a simple trigger. For explanation sake, let's say I have 3 columns in one table ... Col_1, Col_2 and Col_3. The data type...
1
by: Yog | last post by:
I have a datagrid binded to a dataset and once the user updates the grid my dataset is reflected. When i wanted to save the data to tables, however i needed to insert this data into another...
2
by: D Newsham | last post by:
I am trying to run an update query and am having problems with null values. Starting from the beginning - I created a new table from my original table, selecting distinct combinations of these...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
11
by: John | last post by:
Hi I had a working vs 2003 application with access backend. I added a couple fields in a table in access db and then to allow user to have access to these fields via app I did the following; ...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: JDS | last post by:
I have a table with the primary key as a uniqueidentifier / GUID and the rowguid property set (it is used for replication). When creating a data table in a dataset in VS2005 the table adapter...
2
by: sirdavethebrave | last post by:
Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.