Update Query 1

[coachdan]

I have an Access table that was imported from Excel and one of the fields (Site) has a lot of null entries. Now that I have it in Access, I want to have the null cells populated with the site that should be entered. I believe this can be done with an update query, but I am unsure how it works.

I have a second table called Sites that can be used as a lookup table. This is the logic of what I want:

If the "Site" field in the table "RawData"="" Then

Look to the "Homesite" field of the "Site" table

Else (if it is not null)

Leave the "Site" at it's current "RawData" value


Is this possible? How do I set it up?

coachdan32

 

[TonyJollans]

Hi coachdan,

How are the two tables joined - what field on RawData matches what field on the Site table?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Remou]

This is what the query would look like in SQL view, if the tables have a key by which they can be joined:

UPDATE LUTab INNER JOIN XLTab ON LUTab.Key = XLTab.Key 
SET XLTab.Site = [LUTab].[Homesite]
WHERE (((XLTab.Site) Is Null));

Where LUTab is your lookup table and XLTab is the table imported from Excel.

 

[coachdan]

Site" field in tblSites matches "Home Site" in tblRawData. These are the actual names of tables and fields.

coachdan32

 

[TonyJollans]

Hi coachdan,

You should have enough there but post back if you have trouble converting Remou's SQL to use with your tables.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[coachdan]

I have the results I was looking for. Thanks Remou!

coachdan32