If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
counts NULL values in query
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; |
#2
|
|||
|
|||
counts NULL values in query
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... "Nathan" wrote: 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; |
#3
|
|||
|
|||
counts NULL values in query
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; -- Daryl S "Nathan" wrote: 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; |
#4
|
|||
|
|||
counts NULL values in query
Thanks Daryl, this seemed to work. I don't understand WHY it worked, but it
worked nonetheless. "Daryl S" wrote: 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; -- Daryl S "Nathan" wrote: 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; |
#5
|
|||
|
|||
counts NULL values in query
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! -- Daryl S "Nathan" wrote: Thanks Daryl, this seemed to work. I don't understand WHY it worked, but it worked nonetheless. "Daryl S" wrote: 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; -- Daryl S "Nathan" wrote: 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; |
Thread Tools | |
Display Modes | |
|
|