Counting null in totals query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In the following query, the CostQualityOrder table includes categories. Not
all category headings have a matching record in my query. When I run this
query, only categories where P/FCostQuality is not null are counted. How can
I re-write the query to list ALL category headings and for those that have a
null value in the P/FCostQuality field to include it in the query results as
0? I have changed RIGHT JOIN to LEFT JOIN and get the same results. Thanks.

SELECT CostQualityOrder.CostQuality, Count(AllPartsqry.[P/FCostQuality]) AS
CountCQ, AllPartsqry.[P/FCostQuality]
FROM AllPartsqry RIGHT JOIN CostQualityOrder ON AllPartsqry.[P/FCostQuality]
= CostQualityOrder.Order
WHERE (((AllPartsqry.[Make/Buy])="p") AND ((AllPartsqry.Status)=1))
GROUP BY CostQualityOrder.CostQuality, AllPartsqry.[Model#],
AllPartsqry.[P/FCostQuality]
HAVING (((AllPartsqry.[Model#])=[forms]![Rpt_CostQualityQuad]![Model]) AND
((AllPartsqry.[P/FCostQuality]) Is Not Null));
 
Alex said:
In the following query, the CostQualityOrder table includes categories. Not
all category headings have a matching record in my query. When I run this
query, only categories where P/FCostQuality is not null are counted. How can
I re-write the query to list ALL category headings and for those that have a
null value in the P/FCostQuality field to include it in the query results as
0? I have changed RIGHT JOIN to LEFT JOIN and get the same results. Thanks.

SELECT CostQualityOrder.CostQuality, Count(AllPartsqry.[P/FCostQuality]) AS
CountCQ, AllPartsqry.[P/FCostQuality]
FROM AllPartsqry RIGHT JOIN CostQualityOrder ON AllPartsqry.[P/FCostQuality]
= CostQualityOrder.Order
WHERE (((AllPartsqry.[Make/Buy])="p") AND ((AllPartsqry.Status)=1))
GROUP BY CostQualityOrder.CostQuality, AllPartsqry.[Model#],
AllPartsqry.[P/FCostQuality]
HAVING (((AllPartsqry.[Model#])=[forms]![Rpt_CostQualityQuad]![Model]) AND
((AllPartsqry.[P/FCostQuality]) Is Not Null));
Click to expand...


I really don't follow all that, but a couple of observations
may help get you going.

The conditions you have in the HAVING clause should be in
the WHERE clause. The HAVING clause should be used to
filter on the results of an aggregate function, not just the
value in some records.

I'm pretty sure that you want to use a LEFT JOIN.

The aggregate functions all ignore Null values so your
Count(AllPartsqry.[P/FCostQuality]) will not count all
records unless there are no Nulls in that field. The one
exception to that rule is the special syntax Count(*) that
counts records instead of non-Null values.
 
Thanks Marshall - I finally got it working.

Marshall Barton said:
Alex said:
In the following query, the CostQualityOrder table includes categories. Not
all category headings have a matching record in my query. When I run this
query, only categories where P/FCostQuality is not null are counted. How can
I re-write the query to list ALL category headings and for those that have a
null value in the P/FCostQuality field to include it in the query results as
0? I have changed RIGHT JOIN to LEFT JOIN and get the same results. Thanks.

SELECT CostQualityOrder.CostQuality, Count(AllPartsqry.[P/FCostQuality]) AS
CountCQ, AllPartsqry.[P/FCostQuality]
FROM AllPartsqry RIGHT JOIN CostQualityOrder ON AllPartsqry.[P/FCostQuality]
= CostQualityOrder.Order
WHERE (((AllPartsqry.[Make/Buy])="p") AND ((AllPartsqry.Status)=1))
GROUP BY CostQualityOrder.CostQuality, AllPartsqry.[Model#],
AllPartsqry.[P/FCostQuality]
HAVING (((AllPartsqry.[Model#])=[forms]![Rpt_CostQualityQuad]![Model]) AND
((AllPartsqry.[P/FCostQuality]) Is Not Null));
Click to expand...


I really don't follow all that, but a couple of observations
may help get you going.

The conditions you have in the HAVING clause should be in
the WHERE clause. The HAVING clause should be used to
filter on the results of an aggregate function, not just the
value in some records.

I'm pretty sure that you want to use a LEFT JOIN.

The aggregate functions all ignore Null values so your
Count(AllPartsqry.[P/FCostQuality]) will not count all
records unless there are no Nulls in that field. The one
exception to that rule is the special syntax Count(*) that
counts records instead of non-Null values.
Click to expand...
 
Back
Top