counts NULL values in query

[Nathan]

I am suing Access 2003.

I need to a list of all "cases", and a count of "diaries" for each case, and
I need a zero for any null values (ie, zero diaries for that case).

I'm trying the 'Nz' function in sql, but its not working. Any ideas?

SELECT dbo_Case.CaseID, Nz(Count(dbo_Diary.DiaryID),0) AS CountOfDiaryID
FROM dbo_Diary RIGHT JOIN dbo_Case ON dbo_Diary.ClmNum = dbo_Case.CaseID
WHERE (((dbo_Case.CaseEEID)="gclcalg") AND ((dbo_Diary.Dcomplete)=0) AND
((dbo_Case.CaseStatus)<>6 And (dbo_Case.CaseStatus)<>9))
GROUP BY dbo_Case.CaseID;

 

[Nathan]

HAHA - I just reread my post - the first line should say, "I am USING Access
2003". Freudian slip Althought, I'd like to sue them sometimes...

 

[Daryl S]

Nathan -

Count will take into account the null records. I think your problem was the
criteria that dbo_Diary.Dcomplete = 0. That would exclude null records.
Change that to use the nz, like this (untested):

SELECT dbo_Case.CaseID, Count(dbo_Diary.DiaryID) AS CountOfDiaryID
FROM dbo_Diary RIGHT JOIN dbo_Case ON dbo_Diary.ClmNum = dbo_Case.CaseID
WHERE (((dbo_Case.CaseEEID)="gclcalg") AND ((nz(dbo_Diary.Dcomplete,0))=0)
AND
((dbo_Case.CaseStatus)<>6 And (dbo_Case.CaseStatus)<>9))
GROUP BY dbo_Case.CaseID;

 

[Nathan]

Thanks Daryl, this seemed to work. I don't understand WHY it worked, but it
worked nonetheless.

 

[Daryl S]

Nathan -

Using an outer join (LEFT or RIGHT) normally would mean you take all the
records from the (LEFT or RIGHT) table and only those from the other table
that match. If there aren't records from the 'other' table, then those
fields are left blank, but the records from the LEFT or RIGHT table are
included.

The original problem in your code was that you required
dbo_Diary.Dcomplete=0, and this is not a criteria of the LEFT or RIGHT table,
but a criteria on the 'other' table. This means if there is a null value for
Dcomplete (due to a null in the table or in this case no matching record),
then this record will be excluded from the query results.

By adding the nz(Dcomplete,0) to the field, we are saying if this field is
null (either null value or no matching record), then treat it as if it were a
zero. Then your criteria of only including records where Dcomplete = 0 would
be true for these values, and the records would be included in the query
results.

Hope that helps!