title: VBA SQL: update record based on the count of record
body: <p>I have two diffrent tables and i want to update one of them base on several conditions.
  My tables are:</p>
  <p>Table1</p>
  <pre><code>---------------------------
  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
  </code></pre>
  <p>Table2</p>
  <pre><code>--------------------------
  ID   |     MX   |   RN
  --------------------------
  1          33        2
  2          45        3
  3          99        4
  4          67        2
  5          87        4
  </code></pre>
  <p>I want to calculate only those with one N2 = 0 in the table1 using the formula</p>
  <blockquote>
  <p>N2= MX-N1-RN</p>
  </blockquote>
  <p>So for example when ID=1 there is only one 0 so we will sum all the N1 that are not 0</p>
  <blockquote>
  <p>N2=33-(87+22)-2 = -78</p>
  </blockquote>
  <p>and the same in ID=3 and =5</p>
  <blockquote>
  <p>N2=99-(64)-4 = 31</p>
  <p>N2=87-(58+69+36)-4 = -80</p>
  </blockquote>
  <p>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.</p>
  <p>The updated table will be
  Table1</p>
  <pre><code>---------------------------
  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
  </code></pre>
  <p>So I wanted to do it using sql Query but I didn't know how to complete it correctly.</p>
  <p>The code I've done is the following:</p>
  <p>Sql Query:</p>
  <pre><code>UPDATE TABLE1 AS I INNER JOIN TABLE2 AS P ON I.ID = P.ID
  SET I.N2 =P.MX- SUM(I.N2)- (P.RN)
  WHERE (SELECT COUNT(S.ID) FROM TABLE1  AS S
  WHERE S.ID = &quot; &amp; [S.ID] &amp; &quot;
  AND N2 = 0) =1;
  </code></pre>
  <p>VBA code:</p>
  <pre><code>Private Sub GET_CAL()
      DoCmd.SetWarnings False
      DoCmd.OpenQuery &quot;Query1&quot;
      DoCmd.SetWarnings True
  End Sub
  </code></pre>