By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,119 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,119 IT Pros & Developers. It's quick & easy.

Adding another layer of criteria on top of an existing DSum

P: 2
To start, here is my DSum expression:
Develop: Nz(DSum("[DB_" & [CSOs].[Name] & "] + [Ben_" & [CSOs].[Name] & "]","CSO","[Strategic Planning]='Ability to Develop'")*-1,0)

I actually have 4 of these, each one with a different option for [Strategic Planning].
For more context, the [CSOs].[Name] part is drawing from a separate reference table which cycles through plugging in the name for each DB_ and Ben_ field in the CSO table.

What I want to do is add another layer of criteria, the [District] field which exists in the same CSO table. I'd like the results of the above DSum to be grouped according to the District field.

So essentially, my query would have a column on the left with the list of the 8 District options, and the next column would be the Dsum value above, but with the criteria of the District added to it.

For example, if my current DSum above lists a value of 4 for the [Name] "NWASEA" and a value of 3 for the [Name] "NACFODE", but only 2 of NWASEA and 1 of NACFODE are in the [District] Kumi, then my query would show:
District Ability to Develop
Kumi 3

Does this make any sense at all?
Jan 10 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,072
To start with, I would like to point out that you're using the NZ() function in the wrong place, as a Null value in a DB_ or BEN_ field won't be "neutralized".
The proper statement would be:
Expand|Select|Wrap|Line Numbers
  1. DSum("Nz([DB_" & [CSOs].[Name] & "]) + Nz([Ben_" & [CSOs].[Name] & "])","CSO","[Strategic Planning]='Ability to Develop'")*-1,0)
My next remark is the use of the DSUM() function as it's terribly "slow" in comparison with the SUM() in a group by query.

I would start with making a query (e.g. named qryTotalDBDEN) adding the DB_ en BEN_ fields "plainly" like:
Expand|Select|Wrap|Line Numbers
  1. SELECT [District], NZ(DB_1) + Nz([Ben_1] + .... as Total FROM CSO WHERE [Strategic Planning]='Ability to Develop'
Finally I would create a group by query to get the sum like:
Expand|Select|Wrap|Line Numbers
  1. SELECT [District], SUM(Total)*-1 FROM qryTotalDBDEN GROUP BY  [District]
Getting the idea ?

Jan 10 '10 #2

P: 2
Groovy Nico. Thanks for helping out a newb like me... I'll work on this!
Jan 10 '10 #3

Expert 2.5K+
P: 3,072
Keep me posted :-)
By the way, could you change the COS table design ?
Looks to me you would need:
1) District
2) DB
3) BEN
4) CSOs_Name

This would make a simpler query design and would "fit" into normalization rules.

Jan 10 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.