Update Query using if or iff function in MS Acess

[Kannan s]

Dear Sir,
Sub: help requred to build an Update Query using if or iif

I am having two tables with the following fields
I wish to create a single update query in MS Access

Table1: code, qty_x, qty_y, qty_z, qty_o ( code:ch, qty_x,qty_y, qty_z, qty_o : n)
Table2: code, qty, xyz ( code:ch, qty: n, xyz=ch)

Relationship created between the two tables on code field
Table2 contains records of repeated codes with qty, xyz fields as
“ x”, or “y”, or “z” , or “a”, or “b” (other than ‘x’, ‘y’, ‘z’)

Now I wish to update
Table1 qty_x field with the sum of qty of Table2 for xyz field=’x’ for the matching codes,
Table1 qty_y field with the sum of qty of Table2 for xyz field=’y’ for the matching codes,
Table1 qty_z field with the sum of qty of Table2 for xyz field=’z’ for the matching codes,
Table1 qty_o field with the sum of qty of Table2 for xyz fields=
other than ‘x’ ,’y’, ’z’ for the matching codes,

Please help me build up a single query in MS Access to update my database.

Thanking you,

Kannan, S

[PEB]

Hi,
I've seen that in your Update query one of the tables is in agregate State, because you use the agregate function Sum!

So in this case you obtain an recordset that isn't updatable!

So to do an update in this case, you can place the agregate results of your table in a temporary table, using append query

Then you can join the temporary table and the table that you want to update. The relationship have to be in the follow format: one record from the table that you want to update must correspond only to one record of your corresponding table!

I hope that this help you!

Have a nice day!

[PEB]

FOR choosing which value to sum in the first query you can use the function switch(Criteria condition, result, criteria_condition1, result1 and so on...)

[PEB]

IIF also can help you!
Your append query:
INSERT INTO TEMP_TABLE (ID_f1,f2,f3,f4,f5)
SELECT ID, sum(IIF([xyz]="x",[qty],0)), sum(IIF([xyz]="y",[qty],0)), sum(IIF([xyz]="o",[qty],0))
FROM Table2;

[PEB]

INSERT INTO TEMP_TABLE (ID_f1,f2,f3,f4,f5)
SELECT DISTINCT ID, sum(IIF([xyz]="x",[qty],0)), sum(IIF([xyz]="y",[qty],0)), sum(IIF([xyz]="o",[qty],0))
FROM Table2;

OR

INSERT INTO TEMP_TABLE (ID_f1,f2,f3,f4,f5)
SELECT DISTINCT ID, sum(IIF([xyz]="x",[qty],0)), sum(IIF([xyz]="y",[qty],0)), sum(IIF([xyz]="o",[qty],0))
FROM Table2
GROUP BY ID;


:)