473,545 Members | 2,032 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Adding another layer of criteria on top of an existing DSum

2 New Member
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
3 2064
nico5038
3,080 Recognized Expert Specialist
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:
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 ?

Nic;o)
Jan 10 '10 #2
majapa
2 New Member
Groovy Nico. Thanks for helping out a newb like me... I'll work on this!
Jan 10 '10 #3
nico5038
3,080 Recognized Expert Specialist
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)
Jan 10 '10 #4

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

Similar topics

0
3340
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression, or maybe even DCount might be an alternative. I have tried numerous combinations, but with no apparent success. The DSum function relates to a subform (sfrmCost) with a text box (RefCost) which is intended to be the container for a total from a query...
2
11561
by: Dalan | last post by:
I seemed to be having problems with structuring the use of NZ with a DSum expression. Having tried numerous variations of the expression without success, I'm asking for assistance. First some background (Access 97) - the DSum expressions are being used in grand total text boxes on the footer of a subform. And when viewing the subform in the...
47
3320
by: Pierre Barbier de Reuille | last post by:
Please, note that I am entirely open for every points on this proposal (which I do not dare yet to call PEP). Abstract ======== This proposal suggests to add symbols into Python. Symbols are objects whose representation within the code is more important than their actual value. Two symbols needs only to be
3
1609
by: van6 | last post by:
code frag: int main() { float fval1 = 1.0f; float fval2 = 2.0f; double dsum =0.0; // here is the point ; // Are fval1 and fval2 both promoted to type of double prior to adding
3
2982
by: technocraze | last post by:
Hi community experts, I am having an isue with Dsum function that is used to count the total number for a particular field (intake) at the textbox afterupdate event with condition/ criteria (subject). Below is the scenario in details. What I need to do is summing the intake corresponding to what is selected from the dropdown combo box. For...
3
3261
patjones
by: patjones | last post by:
Hi: This is sort of the second part to an earlier question I asked about summing. It's pretty simple. My code is: Me!txtLostDaysNoPaySeasonal = DSum("DateDiff('d', !, !)", "tblWC", " = 'BK21312'") In other words, it's supposed to take the difference in dates for each record, then sum that difference over all records that match the...
13
15879
by: 19bam80 | last post by:
I am using a simple DSum function to add donations made by each member of an organization. It looks like this: dTotal = DSum("SplitAmt", "", " = " & Format(lGiftID)) SplitAmt is the field that contains the amount of the donation. GiftSplits is the name of the table. Gift ID is the field that contains the ID number given to each donor. ...
2
3387
jmoudy77
by: jmoudy77 | last post by:
Hi, I'm trying to use a variable "FirstSemi" as the second criteria in a DSum function. The DSum function is used in the same Form_Load function that the variable was declared. I keep getting an error referencing the variable in the DSum function when the form loads. Any ideas? And > FirstSemi") -Josh
1
1558
by: zeusspandex | last post by:
Hi, i have the following SQL query and would like to add date criteria to it. the field being from the table and the criteria being Between !! And !! query: SELECT , Sum(IIf(,,0)) AS RecycTonnage, Sum() AS TotTonnage,
0
7475
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7437
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7771
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5982
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5343
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4958
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3446
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1023
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
720
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.