No, if you are using aggregate functions they ignore nulls.
SUM([MyNumberField]) works to sum all the values in the field even if some
of the values are null.
Avg does the same thing.
Nulls are propagated if you use the arithmetic operators, but not if you are
using the aggregate functions. For instance,
SELECT Count(FAQ.fID) AS CountOffID
, Count(FAQ.fPriority) AS CountOffPriority
, Count(FAQ.fSubject) AS CountOffSubject
, Count(FAQ.fText) AS CountOffText
, Count(FAQ.fKeywords) AS CountOffKeywords
, Count(FAQ.fLink) AS CountOffLink
FROM FAQ;
Returns
476 - 0 - 476 - 476 - 3 - 155
All the priority fields are null
If I change the query to return no records, I will get all zeroes returned.
FID is the primary key (an autonumber).
SELECT Count(FAQ.fID) AS CountOffID
, Count(FAQ.fPriority) AS CountOffPriority
, Count(FAQ.fSubject) AS CountOffSubject
, Count(FAQ.fText) AS CountOffText
, Count(FAQ.fKeywords) AS CountOffKeywords
, Count(FAQ.fLink) AS CountOffLink
FROM FAQ
WHERE FID is Null
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Klatuu said:
I don't think so. Any calculation that includes a null will return null.
--
Dave Hargis, Microsoft Access MVP
John Spencer said:
No, I disagree.
If I use
Count(Nz([AI].[Act_IssueNo], 0))
I will NEVER see a count of zero. COUNT counts values and zero is a
value -
Nulls don't get counted.
You probably don't need to bother with NZ when you are counting. Since
Count will return zero if all the values in the group are null.
The other aggregate functions will return null if all the values in the
group are null, but again that can be handled by using NZ on the
aggregated
value. I'm not sure it makes any real difference, but , in most cases, I
prefer to use the NZ function as the outer function, so it only gets
called
once (per group) instead of once for every record. Of course, the Access
may be smart enough to move the NZ call as appropriate or the savings in
time may be in the millisecond range for all but really large
aggregations.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
John, there is one error in your code. The Nz is in the wrong place.
It
needs to be enclosing the field, not the count of the field.
TRANSFORM Count(Nz([AI].[Act_IssueNo], 0)) AS CountOfAct_IssueNo
--
Dave Hargis, Microsoft Access MVP
:
Try the following modification of your query. This should give you a
record for every plant in ErgoIssueTbl and the number of Closed
Actions
for
the current year for that plant
TRANSFORM Nz(Count([AI].[Act_IssueNo]), 0) AS CountOfAct_IssueNo
SELECT ErgoIssueTbl.Plant
FROM ErgoIssueTbl LEFT JOIN
(SELECT ActionItemTbl.*
FROM ActionItemtbl
WHERE ActionItemtbl.Act_status ="closed" AND
Year(ActionItemtbl.Act_DateCorr) = Year(Date()) ) as AI
ON ErgoIssueTbl.IssueNum = AI .Act_IssueNo
GROUP BY ErgoIssueTbl.Plant
PIVOT "NumberClosed"
If I have misunderstood your table structure, then obviously this will
not
give you the results you wish to see.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Dave,
Thanks for the reply. I have tried your suggestion and I still get
only
Null out of the query. To be clear, when I run the query I get only
the
column headers back since there is no data for 2008 in the database.
What
I
would like to get is a column listing all plants and a column of
zeros.
:
It is a matter of where you place the Nz. You need to enclose the
individual
field, not the results of the function.
TRANSFORM Count(Nz([ActionItemtbl].[Act_IssueNo],0)) AS
CountOfAct_IssueNo
SELECT ErgoIssueTbl.Plant
FROM ErgoIssueTbl RIGHT JOIN ActionItemtbl ON ErgoIssueTbl.IssueNum
=
ActionItemtbl.Act_IssueNo
WHERE (((ActionItemtbl.Act_status) Like "closed") AND
((ActionItemtbl.Act_DateCorr) Like "*" & Year(Now())))
GROUP BY ErgoIssueTbl.Plant
PIVOT "NumberClosed"
--
Dave Hargis, Microsoft Access MVP
:
Due to the year changing I now have a crosstab query that returns
a
Null.
When the query was built I did not account for the new year. I
would
like
the query to return zeros instead of nulls. I have found several
threads
here on using Nz to do this, but I am missing something and it is
not
working
for me. Any hints are appreciated.
Here is the SQL:
TRANSFORM Nz(Count([ActionItemtbl].[Act_IssueNo]), 0) AS
CountOfAct_IssueNo
SELECT ErgoIssueTbl.Plant
FROM ErgoIssueTbl RIGHT JOIN ActionItemtbl ON
ErgoIssueTbl.IssueNum
=
ActionItemtbl.Act_IssueNo
WHERE (((ActionItemtbl.Act_status) Like "closed") AND
((ActionItemtbl.Act_DateCorr) Like "*" & Year(Now())))
GROUP BY ErgoIssueTbl.Plant
PIVOT "NumberClosed"
