Geek asks and answers

Sum with expression and group in Access 2007 / VBA

Here the code I'm starting with doesn't work like this:

 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) > 500)
 );

I need the total amount and SFS for all instances of the account, where it is more than 500.

For example, if I have the following table

 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

I have to get

 123    1200.00
 234    1605.00

What I get with the above code are different totals, not groups, which means they are not caught using> 500:

 123    350.00
 123    250.00
 234    1605.00

Can anyone help? We have 5 people at a standstill.

+3
source share
3 answers

I needed to use two subqueries. Here is the finale that worked.

UPDATE mt
SET rc='DAR'
WHERE  Account IN
(
  SELECT mt.account
  FROM 
    (
    SELECT mt.Account, SUM(mt.[amount]+Nz(mt.[SFS],0)) as total
    FROM mt
    GROUP BY mt.Account
    )    
  WHERE total>=500
);
0
source

remove pm.[amount] + Nz(pm.[SFS],0)from your group according to the proposal - you do not want to group the amount, only accounts

0
source

Perhaps this is what you are looking for:

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 (SUM(pm.[amount] + Nz(pm.[SFS],0)) > 500)
);
0
source

More articles:


All Articles