473,700 Members | 2,664 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 2073
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 ?

Jan 10 '10 #2
2 New Member
Groovy Nico. Thanks for helping out a newb like me... I'll work on this!
Jan 10 '10 #3
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.

Jan 10 '10 #4

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

Similar topics

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 (qryItems). The query has three number columns from the table (tblItems) of which I have an expression...
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 linked main form the two grand total boxes display #Error if no entry had been made. Those with...
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
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
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 instance if the user choose maths in the combo box, the total no of intake should be displayed in...
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 criteria. The result goes in a locked text box on my main form. Instead, I get an error that says...
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. In the GiftSplits table is another field called Restrictions. Some records in this field contain...
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
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,
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.