Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-18-2018, 01:47 AM   #1
wynstonh
Newly Registered User
 
Join Date: Oct 2016
Posts: 38
Thanks: 7
Thanked 0 Times in 0 Posts
wynstonh is on a distinguished road
sumifs equivalent

Hi,

I am trying to replicate a simple sumifs from excel to access & struggling to make it work. The data I have is:

Ref------ Month---------- Days
1 ----------May 2018----- 12
1 ----------May 2018----- 10
2 ----------May 2018----- 11
2 ----------June 2018----- 30
3 ----------May 2018------ 13
3 ---------May 2018------- 14
3 ---------May 2018------- 15

& what I need is a totals column with the double criteria of ref & month so the output would be as follows:

Ref -------Month----------- Days----- Total
1 ----------May 2018------- 12------- 22
1 ----------May 2018 -------10------- 22
2 ----------May 2018------- 11------- 11
2 ----------June 2018------ 30------- 30
3 ----------May 2018------- 13------- 42
3 ----------May 2018------ 14-------- 42
3 ----------May 2018------ 15-------- 42

I have attempted using DSum but how should this be used with the criteria not being fixed or based on a user entered value?

Thanks for any advice

wynstonh is offline   Reply With Quote
Old 05-18-2018, 01:58 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,068
Thanks: 312
Thanked 706 Times in 672 Posts
Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about
Send a message via Skype™ to Uncle Gizmo
Re: sumifs equivalent

Quote:
Originally Posted by wynstonh View Post
Hi,

I am trying to replicate a simple sumifs from excel to access & struggling to make it work. The data I have is:

Ref------ Month---------- Days
1 ----------May 2018----- 12
1 ----------May 2018----- 10
2 ----------May 2018----- 11
2 ----------June 2018----- 30
3 ----------May 2018------ 13
3 ---------May 2018------- 14
3 ---------May 2018------- 15

& what I need is a totals column with the double criteria of ref & month so the output would be as follows:

Ref -------Month----------- Days----- Total
1 ----------May 2018------- 12------- 22
1 ----------May 2018 -------10------- 22
2 ----------May 2018------- 11------- 11
2 ----------June 2018------ 30------- 30
3 ----------May 2018------- 13------- 42
3 ----------May 2018------ 14-------- 42
3 ----------May 2018------ 15-------- 42

I have attempted using DSum but how should this be used with the criteria not being fixed or based on a user entered value?

Thanks for any advice
I don't get it?

Sent from my SM-G925F using Tapatalk
__________________
Tony Hine - My Aspiration --- Finding a better way of Explaining ---
From this YouTube:-

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Uncle Gizmo is offline   Reply With Quote
Old 05-18-2018, 02:03 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,184
Thanks: 123
Thanked 1,416 Times in 1,388 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: sumifs equivalent

Create a grouped query on the reference and month grouped and the with the days summed. Something like
Code:
SELECT Ref, [Month] , Sum(Days) as RefMonthTotal
FROM YourTable
GROUP BY Ref,[Month]
I would strongly recommend you change the field name of Month to Something else - Month is a reserved word and function in Access and it will trip you up somewhere down the line.

__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
wynstonh (05-18-2018)
Old 05-18-2018, 02:05 AM   #4
wynstonh
Newly Registered User
 
Join Date: Oct 2016
Posts: 38
Thanks: 7
Thanked 0 Times in 0 Posts
wynstonh is on a distinguished road
Re: sumifs equivalent

If I was to do it in excel & my data was in columns A-C my formula in D2 would be:

=SUMIFS(C:C,A:A,$A2,B:B,$B2)

that help?
wynstonh is offline   Reply With Quote
Old 05-18-2018, 02:10 AM   #5
wynstonh
Newly Registered User
 
Join Date: Oct 2016
Posts: 38
Thanks: 7
Thanked 0 Times in 0 Posts
wynstonh is on a distinguished road
Re: sumifs equivalent

absolutely perfect, thanks Minty!

BTW "Month" was just shorthand for my actual field name, but thanks for the advice
wynstonh is offline   Reply With Quote
Old 05-18-2018, 02:12 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,184
Thanks: 123
Thanked 1,416 Times in 1,388 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: sumifs equivalent

You are welcome!
__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-18-2018, 02:23 AM   #7
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,963
Thanks: 54
Thanked 1,920 Times in 1,834 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: sumifs equivalent

You can sum withot grouping:

SELECT Ref, [Month] , DSum("Days", "yourtable","ref=" & ref & " and month=""" & month & """") as RefMonthTotal
FROM YourTable


__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Help. Field Expressions to replicate Sumifs forbzinator Queries 1 11-23-2015 07:34 AM
Sumifs in access JoeC_Glenmark Queries 0 09-25-2013 09:35 PM
Sumproduct(sumifs(indirect IainG Excel 9 03-08-2013 07:15 AM
How to do something similar to excel sumifs formula Draszor Queries 18 09-18-2012 10:06 AM
Equivalent of the "esc" key in VBA... uday Modules & VBA 1 11-20-2001 11:31 PM




All times are GMT -8. The time now is 04:24 PM.


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

Sponsored Links

How to advertise

Media Kit


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