Update Table from Another Table (1 Viewer)

[modest]

Update Table Using a Conversion Table

Two Tables (one is a conversion table) examples below:

Tbl1:
[B]Fld     Location      Conversion[/B]
Virg    Post Office   Virginia
Virg    Government    Virginia
Virg    School        Virginia
Virg    Office        Virginia
Virg                  VA
Mary                  Maryland

Tbl2:
[B]Fld     Location[/B]
Virg    Post Office
Mary    School
Virg    Playground

Want to update Tbl2.Fld to Tbl1.Conversion
Where Tbl1.Fld = Tbl2.Fld AND Tbl1.Location = Tbl2.Location

But if Tbl2.Location is not in Tbl1, Tbl2.Fld = Tbl1.Conversion WHERE Tbl1.Location is Null

----------------------------

It's easy to update where both fields are equal, but cannot quite get it to use the Null value if Location cannot be found in Tbl1.
Note: these are example tables used for demonstration purposes. Real tables are much more complex


Result should look like, where the red was changed:

Tbl2:
[B]Fld      Location[/B]
[COLOR="Red"]Virginia[/COLOR] Post Office
[COLOR="red"]Maryland[/COLOR] School
[COLOR="red"]VA[/COLOR]       Playground

 

[modest]

Solution:

UPDATE Tbl2 

INNER JOIN Tbl1 
ON Tbl2.Fld=Tbl1.Fld 

SET Tbl2.Fld = Tbl1.Conversion

WHERE 
(IIf(Tbl2.Location In (Select Tbl1.Location FROM Tbl1 WHERE Tbl2.Fld=Tbl1.Fld),Tbl2.Location,"")="" AND (Tbl1.Location Is Null))  

OR  
(Tbl2.Fld=Tbl1.Fld AND Tbl2.Location=Tbl1.Location);

Let me know if this has helped any of you (or if there are errors in what I have done), but I believe it is correct.