Append vs Update query? 3

[kevinwilson]

Here's my problem:
I have two similar tables. I would like to populate the empty fields in tblInventory1 with the field info from tblInventory2 (where the SerialNumber's match). I do not want null values carried over from tblInventory2. I still require unmatched SerialNumbers. I've tried Right and Left Outer Joins without success.

tblInventory1
SerialNumber Bldg Room Description
123456 (empty) (empty) Laptop
654321 (empty) 108 Projector
987654 CC (empty) PC
435412 (empty) (empty) Router
unmatchSN DD 305 Router

tblInventory2
SerialNumber Bldg Room Description
123456 AA 101 Laptop
654321 BB (empty) Projector
987654 (empty) 203 PC
435412 AA 305 Router
unmatchSN DD 305 Router

The Appended or Updated tbl should look like this:
SerialNumber Bldg Room Description
123456 AA 101 Laptop
654321 BB 108 Projector
987654 CC 203 PC
435412 AA 305 Router
unmatchSN DD 305 Router

Thanks & Aloha, Kevin

 

[sko]

Kevin,
In your update query you need to use an inner join on sn (only include records from both tables where serial numbers are equal).

Update the fields one at a time and set the criteria to is null (meaning only update the field in table1 if the value is null).

So, my first query looks like this:

Field: Bldg (from table1)
Update to : [table2]![bldg]
Criteria: Is Null

Run that query then modify it changing the fields.

Of course we always do this on a test database

P.S. One thing I'm not clear on. Can you have unmatched records in table2 that have bldg, room, descriptions that are unmatched in table1 i.e.

table1
unmatchSN DD 305 Router

table2
unmatchSN DD 305 Router
unmatchSN AA 100 Router <---No corresponding record?



If so, you need to find those records and append. Let me know.

 

[kevinwilson]

SKO-
Thanks, it worked! Somewhere along the line I missed a step, seeing it from someone else made it a lot easier.

Aloha
~kevin