Re: Group By Problem - show row even when count = 0

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks Duane - I believe I am concerned with there being no value in the
business column of the query (and therefore the row is not visible).

That doesn't seem to have worked - it shows 0/0 for 'Post Phase 2 - High'
but nothing for post phase 2 critical and low - could it be something else
that is causeing the problem.

From investigating there appear to be no items in the database that are post
phase 2 and critical (there are other catagories other than business / test
that the item could be assigned to) - I tried putting a NZ around all parts
of the query but that made no difference

The results are as follows:-

RequiredFor Severity/Closure Business Test
Phase 1 1 Critical 6 0
Phase 1 2 High 8 0
Phase 1 3 Medium 3 0
Phase 1 4 Low 0 0
Phase 2 1 Critical 2 0
Phase 2 2 High 2 0
Phase 2 3 Medium 14 0
Phase 2 4 Low 0 0
Post Phase 2 2 High 0 0
Post Phase 2 3 Medium 1 0


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uPZH5fT7GHA.3960@xxxxxxxxxxxxxxxxxxxxxxx
"If there are no items assigned to the business it doesn't show the row"
do
you actually mean it doesn't show the "row" or are you just concerned
there
is no value in the Business column of the query? Try something like the
following if you only want to display a zero where there was a null.

SELECT tblApprovedDefects.RequiredFor,
tblApprovedDefects.[Severity/Closure],
Nz(Sum(IIf((LookupResource([AssignedToContact]))="Business",1,0)),0) AS
Business,
Nz(Sum(IIf((LookupResource([AssignedToContact]))="Test",1,0)),0) AS Test
FROM tblApprovedDefects
WHERE (((tblApprovedDefects.ProblemStatus) Not In ("Rejected","Closed")))
GROUP BY tblApprovedDefects.RequiredFor,
tblApprovedDefects.[Severity/Closure];


--
Duane Hookom
MS Access MVP


From: "Andibevan" <andibevan@xxxxxxxxxxxxxxxxxx>
Subject: Group By Problem - show row even when count = 0
Date: Wednesday, October 11, 2006 4:40 AM

I am using the following query:-

SELECT tblApprovedDefects.RequiredFor,
tblApprovedDefects.[Severity/Closure],
Sum(IIf((LookupResource([AssignedToContact]))="Business",1,0)) AS
Business,
Sum(IIf((LookupResource([AssignedToContact]))="Test",1,0)) AS Test
FROM tblApprovedDefects
WHERE (((tblApprovedDefects.ProblemStatus) Not In ("Rejected","Closed")))
GROUP BY tblApprovedDefects.RequiredFor,
tblApprovedDefects.[Severity/Closure];

This ends up with a seperate row in the query for each [Severity/Closure]
and then counts the number of items assigned to Bussiness or Test.

If there are no items assigned to the business it doesn't show the row at
all and only shows Test - how do I modify the query so that it shows both
the business and test rows, even if there are no items to count?

Sorry if this isn't very clear but I am not quite sure how to explain what
I
want - happy to clarify.

TIA

Andi



"Andibevan" <andibevan@xxxxxxxxxxxxxxxxxx> wrote in message
news:uutP$kR7GHA.2248@xxxxxxxxxxxxxxxxxxxxxxx
I am using the following query:-

SELECT tblApprovedDefects.RequiredFor,
tblApprovedDefects.[Severity/Closure],
Sum(IIf((LookupResource([AssignedToContact]))="Business",1,0)) AS
Business,
Sum(IIf((LookupResource([AssignedToContact]))="Test",1,0)) AS Test
FROM tblApprovedDefects
WHERE (((tblApprovedDefects.ProblemStatus) Not In
("Rejected","Closed")))
GROUP BY tblApprovedDefects.RequiredFor,
tblApprovedDefects.[Severity/Closure];

This ends up with a seperate row in the query for each
[Severity/Closure]
and then counts the number of items assigned to Bussiness or Test.

If there are no items assigned to the business it doesn't show the row
at
all and only shows Test - how do I modify the query so that it shows
both
the business and test rows, even if there are no items to count?

Sorry if this isn't very clear but I am not quite sure how to explain
what
I
want - happy to clarify.

TIA

Andi







.

Relevant Pages

  • Re: Need to select a certain X records after a query in access
    ... Duane Hookom ... > business days prior to the date the user enters. ... >>> query to generate all of the records from the START DATE to 15 BUSINESS ...
    (microsoft.public.access.queries)
  • Re: Help with Date Parameter
    ... "Enter Business Date of Report"? ... When I run the query, records for all dates are displayed instead of just ... You need to set the query criteria to: ... Brackets will not be necessary (but are still good ...
    (microsoft.public.access.queries)
  • Re: Do I need an Union Query for this?
    ... Shouldn't need a union query. ... Just use a DLookup function and the Nz ... because one ContactID can have multiple addresses ... > and their home takes priority over their business, ...
    (microsoft.public.access.queries)
  • Re: Cant get the proper display of a field in my report.
    ... not have a lookup field in your tables, ... think the problem is in your query. ... is for businesses I have a field in my contacts ... that has a number format so it can be used as a foreign key for the ...
    (microsoft.public.access.reports)
  • Re: Group By Problem - show row even when count = 0
    ... Are there "Business" records for post phase 2 critical and low? ... Use this in your query to all records form ... FROM tblApprovedDefects ...
    (microsoft.public.access.queries)