469,589 Members | 2,109 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,589 developers. It's quick & easy.

Update Query using if or iff function in MS Acess

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
Aug 7 '06 #1
4 15292
PEB
1,418 Expert 1GB
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!
Aug 31 '06 #2
PEB
1,418 Expert 1GB
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...)
Aug 31 '06 #3
PEB
1,418 Expert 1GB
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;
Aug 31 '06 #4
PEB
1,418 Expert 1GB
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;


:)
Aug 31 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Reply via newsgroup | last post: by
36 posts views Thread by Liam.M | last post: by
3 posts views Thread by Michel Esber | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.