Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Visual Basic (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=68)
-   -   Sum Group By (https://www.access-programmers.co.uk/forums/showthread.php?t=289800)

MDD 10-08-2016 05:02 AM

Sum Group By
 
Hi

I'd like to use a formula similar to SumIf in Access.
I have column 1 with product names and col 2 with Totals.

I would like to show totals for each product in col2.
Just unable to make this happen :banghead:

I've used code like this -
DoCmd.RunSQL "UPDATE Table1 SET Table1.Col2 = Select Sum(Table1.Col2) FROM Table1 Group By Table1.Col1"

However this throws an error.

Any help much appreciated. Thank you.

CJ_London 10-08-2016 06:27 AM

Re: Sum Group By
 
Your query is all wrong but before making any suggestions, I have to ask why you are trying to do this and what you expect the final outcome to be (provide some examples) - it is not normal practice to store calculated values in tables - you would calculate in a query as and when required.

In addition, you cannot use group by queries in update queries so any solution is likely to be more complex than you think - or use a dsum domain function which will be slow

MDD 10-08-2016 06:33 AM

Re: Sum Group By
 
Thanks for your reply.

I have a very large dataset that needs sifting.
For instance there are equal and opposite entries, i.e. debit and credit entries in 2 columns that may relate to the same product (say col3).

Hence I need a sumif equivalent to get rid of products that have net zero balance.

CJ_London 10-08-2016 06:52 AM

Re: Sum Group By
 
probably easier to create two queries, one based on col1 and one on the sum col2, then join them together - on col3?

get it working first as a query to make sure you get the correct syntax and values required before putting into VBA

it might look something like

Code:

UPDATE O SET O.Col2 = S.sumofcol2
FROM Table1 O INNER JOIN (SELECT Col1, Select Sum(Col2) AS sumofcol2 FROM Table1 Group By Col1) S
ON O.col1=S.col1


MDD 10-08-2016 07:18 AM

Re: Sum Group By
 
Thanks for this

However, it does not work as a query as well. Wish I couldve done this in Excel.

MDD 10-09-2016 07:33 AM

Re: Sum Group By
 
Anyone who is able to help with a solution?


All times are GMT -8. The time now is 08:59 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World