423,818 Members | 2,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,818 IT Pros & Developers. It's quick & easy.

Update Query using if or iff function in MS Acess

P: 1
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
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
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!
Aug 31 '06 #2

PEB
Expert 100+
P: 1,418
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...)
Aug 31 '06 #3

PEB
Expert 100+
P: 1,418
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;
Aug 31 '06 #4

PEB
Expert 100+
P: 1,418
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;


:)
Aug 31 '06 #5

Post your reply

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