VBA SQL: update record based on the count of record -

i have 2 diffrent tables , want update 1 of them base on several conditions. tables are:

table1

--------------------------- id   |     n1   |    n2 --------------------------- 1          22        12 1           5        0 1          87        12 2          67        0 2           6        0 2           3        0 2          60        12 3          55        0 3          64        12 4           8        0 4          75        12 4           4        0 5          58        12 5          69        12 5          36        12 5           3        0 

table2

-------------------------- id   |     mx   |   rn -------------------------- 1          33        2 2          45        3 3          99        4 4          67        2 5          87        4 

i want calculate 1 n2 = 0 in table1 using formula

n2= mx-n1-rn

so example when id=1 there 1 0 sum n1 not 0

n2=33-(87+22)-2 = -78

and same in id=3 , =5

n2=99-(64)-4 = 31

n2=87-(58+69+36)-4 = -80

then n2 of id updated new record. id=2 ignored because there 3 records = 0 , id=4 there 2 records =0.

the updated table table1

--------------------------- id   |     n1  |     n2 --------------------------- 1          22        12 1           5       -78 1          87        12 2          67         0 2           6         0 2           3         0 2          60        12 3          55        31 3          64        12 4           8         0 4          75        12 4           4         0 5          58        12 5          69        12 5          36        12 5           3       -81 

so wanted using sql query didn't know how complete correctly.

the code i've done following:

sql query:

update table1 inner join table2 p on i.id = p.id set i.n2 =p.mx- sum(i.n2)- (p.rn) (select count(s.id) table1  s s.id = " & [s.id] & " , n2 = 0) =1; 

vba code:

private sub get_cal()     docmd.setwarnings false     docmd.openquery "query1"     docmd.setwarnings true end sub 

your calculation within sql statement seems bit off. want get

n2 = mx - sum(n1=0) - rn

your sql statement give you

n2 = mx - sum(n2) - rn

you need @ least 2 nested queries achieve goal:

query1 count zeros in table1.n2 each single id.

query2 give ids single corresponding n2 zero.

both nested inside update query. use dlookup , dsum need:

update table1 t3 set t3.n2 = dlookup("mx","table2","id = " & t3.id) - dsum("n1","table1","id = " & t3.id & " , n2 <>0") - dlookup("rn","table2","id = " & t3.id) t3.n2 = 0 , t3.[id]       in (select t2.id           (select t1.id, t1.n2                 table1 t1                 t1.n2 = 0)  t2           group t2.id           having count(t2.n2)=1) 

dlookup corresponding mx , rn values. dsum function sum values corresponding id n2 not 0.

i think of solution without dfunctions involve more nested queries calculated fields.