Vba SQL: update record based on the count of record

See more:

SQL

VBA

I have two different tables and I want to update one of them base on several conditions. My 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 to calculate only those with one N2 = 0 in the table1 using the formula


N2= MX-N1-RN

So for example when ID=1 there is only one 0 so we will sum all the N1 that are not 0



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

and the same in ID=3 and =5


N2=99-(64)-4 = 31

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

Then N2 of that ID will be updated with the new record. ID=2 will be ignored because there are three records = 0 and so for ID=4 there are two records =0.

The updated table will be 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      -80

What I have tried:

So I wanted to do it using sql Query but I didn't know how to complete it correctly.

The code I've done is the following:

Sql Query:

UPDATE TABLE1 AS I INNER JOIN TABLE2 AS P 
ON I.ID = P.ID
 SET I.N2  = IIF(I.N2 = -0, 
(P.MX - SUM(DMax("N2 ", "TABLE1 ", 
"ID=" & I.ID)) - P.RN), I.N2 )
WHERE I.[ID] IN 
             (SELECT B.[ID] 
              FROM TABLE1 B
              GROUP BY B.[ID] 
              HAVING (Count(B.[ID]) > 1)
                AND (Min(B.N2 ) <> Max(B.N2 ))
                AND (Min(B.N2 ) = 0 OR Max(B.N2 ) = 0))
AND I.N2 IN (0);

VBA code:

Private Sub GET_CAL()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Query1"
    DoCmd.SetWarnings True
End Sub