Group By Problem - show row even when count = 0

[Andibevan]

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

 

[Duane Hookom]

"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" <[email protected]>
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]

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

"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" <[email protected]>
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

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

 

[Duane Hookom]

Are there "Business" records for post phase 2 critical and low?
Are there "Test" records for post phase 2 critical and low?

Your original question suggested there were records in at least one of
these. If you want records for absolutely every combination of RequiredFor
and Severity/Closure then you may need to create a cartesian query that
returns every unique combination. Use this in your query to all records form
the cartesian query.

--
Duane Hookom
MS Access MVP

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

"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" <[email protected]>
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

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