Adding another layer of criteria on top of an existing DSum

[majapa]

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?

[nico5038]

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 "neutralize d".
The proper statement would be:

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:

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:

SELECT [District], SUM(Total)*-1 FROM qryTotalDBDEN GROUP BY  [District]

Getting the idea ?

Nic;o)

[majapa]

Groovy Nico. Thanks for helping out a newb like me... I'll work on this!

[nico5038]

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.

Nic;o)