Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-12-2019, 01:52 PM   #1
hvaugh00
Newly Registered User
 
Join Date: Jun 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
hvaugh00 is on a distinguished road
Post DSUM Syntax with multiple Criteria

Hi – I’m really struggling to get this DSum formula to work and I’m hoping someone can help. I would be so grateful!

What I’m attempting to do is sum the Actual Alloc to Total Division based on the combination of fields COMBO and MBU and whether Mcdonald’s is included.

Table 1 (0014a update actual SMA expenses table)
-Contains Actual Allocations to Total Division
-Combo
-Include McDonalds

Table 2 (0014 AFE PA Data w/ Data Combos Table)
-MBU
-Include McD

Where Combo = MBU and Include McDonald’s = Include McD, I want to return the sum of the Actual Alloc to Total Division. The formula I came up with is returning the complete total for Actual Alloc to Total Division regardless of the 2 criteria.

My formula:
MBU test: DSum("[Actual Alloc to Total Division]","0014a update actual SMA expenses table","COMBO='" & [0014 AFE PA DATA w/ Data Combos table]![MBU] & "'" And "Include McDonalds='" & [0014 AFE PA DATA w/ Data Combos table]![Include Mcd] & "'")

Thanks in advance!!

hvaugh00 is offline   Reply With Quote
Old 06-12-2019, 01:54 PM   #2
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,046 Times in 1,027 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DSUM Syntax with multiple Criteria

Hi. Welcome to the forum. Are you entering this expression in a query? If so, can you show us the whole query? Thanks.
__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-13-2019, 05:10 AM   #3
hvaugh00
Newly Registered User
 
Join Date: Jun 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
hvaugh00 is on a distinguished road
Re: DSUM Syntax with multiple Criteria

Hi! Thanks for replying. Attached is a screenshot from my query.
Attached Images
File Type: png qry.PNG (56.7 KB, 10 views)

hvaugh00 is offline   Reply With Quote
Old 06-13-2019, 07:09 AM   #4
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,046 Times in 1,027 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DSUM Syntax with multiple Criteria

Quote:
Originally Posted by hvaugh00 View Post
Hi! Thanks for replying. Attached is a screenshot from my query.
What do you get if you simply connect those two fields as well and not use DSum()? If you add those two connections/links, you can try using Sum instead of Group By.
__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-13-2019, 09:17 AM   #5
hvaugh00
Newly Registered User
 
Join Date: Jun 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
hvaugh00 is on a distinguished road
Re: DSUM Syntax with multiple Criteria

Thank you! That worked. The issue is that I have 9 other Combos (besides MBU) that I'm going to have to create different queries for and then append all together since I can't get the Dsum syntax right
hvaugh00 is offline   Reply With Quote
Old 06-13-2019, 09:23 AM   #6
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,046 Times in 1,027 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DSUM Syntax with multiple Criteria

Quote:
Originally Posted by hvaugh00 View Post
Thank you! That worked. The issue is that I have 9 other Combos (besides MBU) that I'm going to have to create different queries for and then append all together since I can't get the Dsum syntax right
Hi. If we knew more about your database, we might be able to offer a better solution. Unfortunately, you're the only one who knows more about it at this time.

__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Tags
dsum , dsum running total , dsum() function

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DSUM Syntax with multiple Criteria DataBass Reports 17 06-24-2018 08:29 PM
DSUM with multiple criteria gladiater General 3 03-24-2013 10:54 AM
DSUM Multiple Criteria Syntax issues Steve547 Modules & VBA 3 02-05-2013 01:52 PM
Dsum with multiple criteria Unicon Queries 4 10-22-2012 06:09 PM
DSUM criteria syntax highandwild Queries 0 04-26-2010 10:20 AM




All times are GMT -8. The time now is 07:19 PM.


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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World