Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-08-2016, 05:02 AM   #1
MDD
Newly Registered User
 
Join Date: Oct 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
MDD is on a distinguished road
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

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.

MDD is offline   Reply With Quote
Old 10-08-2016, 06:27 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,466
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-08-2016, 06:33 AM   #3
MDD
Newly Registered User
 
Join Date: Oct 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
MDD is on a distinguished road
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.

MDD is offline   Reply With Quote
Old 10-08-2016, 06:52 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,466
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-08-2016, 07:18 AM   #5
MDD
Newly Registered User
 
Join Date: Oct 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
MDD is on a distinguished road
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 is offline   Reply With Quote
Old 10-09-2016, 07:33 AM   #6
MDD
Newly Registered User
 
Join Date: Oct 2016
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
MDD is on a distinguished road
Re: Sum Group By

Anyone who is able to help with a solution?

MDD is offline   Reply With Quote
Reply

Tags
group by , sum query , update

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you hide group total if group field is blankis null bignose2 Reports 2 09-01-2015 03:50 AM
Referencing Group Footer Calculated Field in Report Group Header rmoreno Reports 7 03-17-2008 08:15 PM
Calculate partial totals per report group according to criteria specified in group fugifox Reports 4 01-17-2008 09:06 AM
Group Headings Based on Date of Group magic chef Reports 3 09-16-2005 01:49 PM
start at page 1 after a group and group according to number of pages in group blc Reports 5 07-16-2002 04:30 PM




All times are GMT -8. The time now is 03:10 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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