CODEWITHSUNDEEP
ms-accessms-access-2010
Andrew Martin
Andrew Martin

Reputation: 5761

Writing a Iif statement to update a table

I am using Access 2010.

I am trying to do an operation between three tables. The first and second table have an identical ID field, and so are joined using this. The second and third table have the IDs of departments, and so are joined using this. Not all of the IDs in the second table exist in the third table, though.

I am trying to run a query that will update a field in the first table. The query will determine whether the Department ID in the second table exists in the third table. If it does, it uses another field in that third table (Department ID Full Name) to update the first table. If it doesn't, it uses the existing Department ID in the second table to update the first table.

This is what I've got so far:

Field: Relevant field in Table 1
Table: Table 1
Update To: IIf([Table 2].[Dept ID] In (SELECT [Table 3].[Dept ID] FROM [Table 3]),[Table 3].[Dept ID Full Name],[Table 2].[Dept ID])

I've never written anything like this before and am totally new to Iif queries, so am not sure if this is even remotely close! Currently I have an error: "Operation must use an updateable query".

Edit - In SQL:

UPDATE 
    ([Table 1] INNER JOIN 
        [Table 2 ON [Table 1].[ID] = [Table 2].[ID]) INNER JOIN 
            [Table 3] ON [Table 2].[Dept ID] = [Table 3].[Dept ID] 

SET [Table 1].[Dept] = 

IIf([Table 2].[Dept ID] In (SELECT [Table 3].[Dept ID] FROM [Table 3]),
[Table 3].[Dept Name],[Table 2].[Dept ID]);

Upvotes: 1

Views: 4171

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123809

If not all of the ID's that are in [Table 2] exist in [Table 3] then a solution involving a LEFT (or RIGHT) JOIN may be required. I just tried the following SQL statement and it seems to be working for me:

UPDATE 
    (
        [Table 1] 
        INNER JOIN 
        [Table 2] 
            ON [Table 1].Key1 = [Table 2].Key1
    ) 
    LEFT JOIN 
    [Table 3] 
        ON [Table 2].[Dept ID] = [Table 3].[Dept ID] 
SET 
    [Table 1].FieldToUpdate = IIf(IsNull([Table 3].[Dept ID]),[Table 2].[Dept ID],[Table 3].[Dept ID Full Name]);

(I made up some of my own column names before you edited your question, so you'll have to adjust to suit.)

Upvotes: 2

Related Questions