title: Sum with expression and group by in Access 2007 / VBA
body: <p>Here's the code I'm starting with, that doesn't work quite right:</p>
  
  <pre><code> UPDATE mt
   SET mt.action = 'A', mt.TQA = TRUE,
       mt.OPID = 'SYS', mt.rc= 'DAR', mt.h='DAR'
   WHERE EXISTS 
   (
       SELECT mt.Account FROM mt AS pm
       WHERE mt.Account = pm.Account
       GROUP BY pm.Account, pm.[amount] + Nz(pm.[SFS],0)
       HAVING (pm.[amount] + Nz(pm.[SFS],0) &gt; 500)
   );
  </code></pre>
  
  <p>what I need is a total sum of amount and SFS for all instances of the account, where that's more than 500.</p>
  
  <p>For example, if I have the following table</p>
  
  <pre><code> Account   Amount   SFS
   123       350.00   0.00
   123       125.00   125.00
   123       350.00   0.00
   123       125.00   125.00
   234       1600.00  5.00
   345       2.50     4.60
  </code></pre>
  
  <p>I should get</p>
  
  <pre><code> 123    1200.00
   234    1605.00
  </code></pre>
  
  <p>What I get with the code above is the different totals alone, not groups, which means they're not caught by the >500:</p>
  
  <pre><code> 123    350.00
   123    250.00
   234    1605.00
  </code></pre>
  
  <p>Can anyone help? This has 5 of us stumped.</p>