Solved

MS Access Sum Query- need a 0 return

Posted on 2008-10-29
13
178 Views
Last Modified: 2012-06-21
I have the following query below. The items are listed by Grouping, there is 4 possible groupings. i am attempted to sum up SumOfCC_CostEstimate by Grouping.  The count query works fine for each item that has least onc record, but ignores records where there is not matches.

I want to dislay all the possible groups, and if there is no records I want to show 0 in the SumOfCC_CostEstimate, in my example thewre is no records matching ProjectDisposition="On-Hold" and tblProjects.NLG)=Yes. I want to disaply

ProjectDisposition                          SumOfCC_CostEstimate
Cancelled                                                128000
Completed                                                 30000
Pending ITSC approval/prioritization      130000
On-Hold                                                   0 ' This is missing
SELECT tblProjects.ProjectDisposition, Sum(tblSizing.CC_CostEstimate) AS SumOfCC_CostEstimate
FROM (tblProjects LEFT JOIN tLUProjectStatus ON tblProjects.ProjectDisposition = tLUProjectStatus.DispositionDescription) LEFT JOIN tblSizing ON tblProjects.PID = tblSizing.PID
WHERE (((tblProjects.NLG)=Yes)) OR (((tblProjects.NLG)=Yes)) OR (((tblProjects.NLG)=Yes)) OR (((tblProjects.NLG)=Yes))
GROUP BY tblProjects.ProjectDisposition
HAVING (((tblProjects.ProjectDisposition)="Pending ITSC approval/prioritization")) OR (((tblProjects.ProjectDisposition)="Completed")) OR (((tblProjects.ProjectDisposition)="Cancelled")) OR (((tblProjects.ProjectDisposition)="On-Hold"));

Open in new window

0
Comment
Question by:RalphyC
  • 5
  • 4
  • 4
13 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22835964
Might not be pretty, but this should work:
select ProjectDisposition, iif(isnull(SumOfCC_CostEstimate),0,SumOfCC_CostEstimate)
from (
SELECT tblProjects.ProjectDisposition,
       Sum(tblSizing.CC_CostEstimate) AS SumOfCC_CostEstimate
FROM tblProjects 
     LEFT JOIN tLUProjectStatus ON tblProjects.ProjectDisposition = tLUProjectStatus.DispositionDescription
     LEFT JOIN tblSizing ON tblProjects.PID = tblSizing.PID
WHERE tblProjects.NLG=Yes
GROUP BY tblProjects.ProjectDisposition
HAVING (tblProjects.ProjectDisposition)="Pending ITSC approval/prioritization"
    OR (tblProjects.ProjectDisposition)="Completed"
    OR (tblProjects.ProjectDisposition)="Cancelled"
    OR (tblProjects.ProjectDisposition)="On-Hold"
)

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22835970
Or slightly better:
select ProjectDisposition, iif(isnull(SumOfCC_CostEstimate),0,SumOfCC_CostEstimate)
from (
SELECT tblProjects.ProjectDisposition,
       Sum(tblSizing.CC_CostEstimate) AS SumOfCC_CostEstimate
FROM tblProjects 
     LEFT JOIN tLUProjectStatus ON tblProjects.ProjectDisposition = tLUProjectStatus.DispositionDescription
     LEFT JOIN tblSizing ON tblProjects.PID = tblSizing.PID
WHERE tblProjects.NLG=Yes
    and ( (tblProjects.ProjectDisposition)="Pending ITSC approval/prioritization"
          OR (tblProjects.ProjectDisposition)="Completed"
          OR (tblProjects.ProjectDisposition)="Cancelled"
          OR (tblProjects.ProjectDisposition)="On-Hold"
        )
GROUP BY tblProjects.ProjectDisposition
)

Open in new window

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22835995
If I understand your problem you do have a row in your tblProjects for the On-Hold, but you don't have any data in tblSizing, so the sum return you null?

If you get a null value, simply add a coalesce in your sum : Sum(COALESCE(tblSizing.CC_CostEstimate,0)) then if you get null for the tblSizing.CC_CostEstimate it will be replace by 0 then the sum will give you 0
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:jfmador
ID: 22836019
Forget about my coalesce it didn't exists under access, terry is right you should use IIF and ISNULL

but I would suggest
SELECT tblProjects.ProjectDisposition,
Sum(IIF(ISNULL(tblSizing.CC_CostEstimate),0,tblSizing.CC_CostEstimate)) AS SumOfCC_CostEstimate
FROM (tblProjects 
     LEFT JOIN tLUProjectStatus ON tblProjects.ProjectDisposition = tLUProjectStatus.DispositionDescription)
     LEFT JOIN tblSizing ON tblProjects.PID = tblSizing.PID
WHERE tblProjects.NLG=Yes
GROUP BY tblProjects.ProjectDisposition
HAVING (tblProjects.ProjectDisposition)="Pending ITSC approval/prioritization"
    OR (tblProjects.ProjectDisposition)="Completed"
    OR (tblProjects.ProjectDisposition)="Cancelled"
    OR (tblProjects.ProjectDisposition)="On-Hold"

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22836026
Was just about to suggest that myself, thanks to your first suggestion! :-)
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22836144
Yeah I really like COALESCE, I never know how to prononce it and what it really means, Is it a real word? enyway it is a neat function.

sorry i'm french :)
0
 

Author Comment

by:RalphyC
ID: 22836155
jfmador

Using your last example, I am still getting only 3 rows returned. To make this clear, tblprojects does not have any records that match  (ProjectDisposition="On-Hold" and tblProjects.NLG=Yes). Table tblSizing is only used for purpose of extracting the CC_CostEstimate if it has matching PID from table tblProjects, and it is a 1 to 1 relationship with tblProjects. Please advise.
ProjectDisposition                          SumOfCC_CostEstimate
Cancelled                                                128000
Completed                                                 30000
Pending ITSC approval/prioritization      130000
On-Hold                                                   0 ' This is still missing
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22836201
ok then you need a table with a row containing ProjectDisposition = "On-Hold"

you can use the same table twice to do it
SELECT tblProjects.ProjectDisposition, IIF(ISNULL(SumOfCC_CostEstimate),0,SumOfCC_CostEstimate) 
FROM tblProjects LEFT JOIN (
SELECT tblProjects.ProjectDisposition,
Sum(tblSizing.CC_CostEstimate) AS SumOfCC_CostEstimate
FROM (tblProjects 
     LEFT JOIN tLUProjectStatus ON tblProjects.ProjectDisposition = tLUProjectStatus.DispositionDescription)
     LEFT JOIN tblSizing ON tblProjects.PID = tblSizing.PID
WHERE tblProjects.NLG=Yes
GROUP BY tblProjects.ProjectDisposition
) t on tblProjects.ProjectDisposition = t.ProjectDisposition
WHERE (tblProjects.ProjectDisposition) = "Pending ITSC approval/prioritization"
    OR (tblProjects.ProjectDisposition)="Completed"
    OR (tblProjects.ProjectDisposition)="Cancelled"
    OR (tblProjects.ProjectDisposition)="On-Hold" 

Open in new window

0
 

Author Comment

by:RalphyC
ID: 22836240
This works, but it totals all records from table projects, regardelss of the tblProjects.NLG=Yes. If I add this to the criteria tblProjects.NLG=Yes, the On-Hold ), no longer appears.
0
 

Author Comment

by:RalphyC
ID: 22836278
Actually all now I need to so is display the distinct records, since they are repeating well over 100 times.
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 22836293
I think jfmador's initial suggestion for the query is ok, as long as the filters in the "having" section are shifted to the "where" section
SELECT tblProjects.ProjectDisposition,
Sum(IIF(ISNULL(tblSizing.CC_CostEstimate),0,tblSizing.CC_CostEstimate)) AS SumOfCC_CostEstimate
FROM (tblProjects 
     LEFT JOIN tLUProjectStatus ON tblProjects.ProjectDisposition = tLUProjectStatus.DispositionDescription)
     LEFT JOIN tblSizing ON tblProjects.PID = tblSizing.PID
WHERE tblProjects.NLG=Yes
  and ( (tblProjects.ProjectDisposition)="Pending ITSC approval/prioritization"
        OR (tblProjects.ProjectDisposition)="Completed"
        OR (tblProjects.ProjectDisposition)="Cancelled"
        OR (tblProjects.ProjectDisposition)="On-Hold"
      )
GROUP BY tblProjects.ProjectDisposition

Open in new window

0
 

Author Comment

by:RalphyC
ID: 22836296
Got it, just add DISTINCT and it will do the trick.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22836342
I think jfmador deserves some points - if you post a request to reopen the question in the community support zone with the url of this question, they will reopen it so you can split the points.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question