need count to return a zero value

[Nathan]

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[Duane Hookom]

Why don't you have a table of unique ClmNum values? I expect you could get by
without one by using a query:

SELECT dbo_Diary.ClmNum
FROM dbo_Diary
GROUP BY dbo_Diary.ClmNum;

 

[KARL DEWEY]

Is your dbo_Diary.Dcomplete field a text or number field? If text then
surround the number zero with quotes.

 

[KenSheridan via AccessMonster.com]

There is a logical inconsistency in what you are currently asking the query
to do, in that you are restricting the results on two other columns, which
might mean that there are no rows with those values and the value of the
grouped column by which you are also restricting the query. Consequently no
row would be returned in which to include the zero count.

One way to do it would be to return the distinct value of the parameter in an
outer query, and the count of the rows which match the parameter value and
the other criteria in a subquery:

SELECT DISTINCT
[Forms]![frmUpdateCaseentry]![CaseID] AS ClmNum,
(SELECT COUNT(*)
FROM dbo_Diary
WHERE ClmNum = [Forms]![frmUpdateCaseentry]![CaseID]
AND Dtype = "Subro"
AND Dcomplete=0) AS DiaryCount
FROM dbo_Diary;

For convenience the outer query uses the same table as the subquery here, but
it could in fact use any table, so you might like to create a table, named
Dummy say, with one column and one row of an arbitrary value, and use that
table in the outer query, which would mean you could then dispense with the
DISTINCT option as the outer query can only return one row

If you did not want to restrict the query by the parameter, but return all
ClmNum values, you could of course correlate the subquery with the outer
query on the ClmNum column:

SELECT DISTINCT ClmNum,
(SELECT COUNT(*)
FROM dbo_Diary As D2
WHERE D2.ClmNum = D1.ClmNum
AND Dtype = "Subro"
AND Dcomplete=0) AS DiaryCount
FROM dbo_Diary AS D1;

In this case the outer query and subquery must use the same table of course,
differentiating them with aliases D1 and D2.

Ken Sheridan
Stafford, England

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[Nathan]

Its actually a Y/N field.

Is your dbo_Diary.Dcomplete field a text or number field? If text then
surround the number zero with quotes.

--
Build a little, test a little.

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[Nathan]

I do have such a table, but it wasn't germane to the query I was running.
Should I join that table?

Why don't you have a table of unique ClmNum values? I expect you could get by
without one by using a query:

SELECT dbo_Diary.ClmNum
FROM dbo_Diary
GROUP BY dbo_Diary.ClmNum;

--
Duane Hookom
Microsoft Access MVP

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[Duane Hookom]

My initial thought was to do an LEFT or RIGHT JOIN but you stated "I have no
sceond table, so an outer join won't do the trick for me". Now apparently
you have a table to join to that selects all records from the ClmNum table.
You may have to change the Having to a Where clause and go against the ClmNum
table rather than the dbo_Diary table.
--
Duane Hookom
Microsoft Access MVP

I do have such a table, but it wasn't germane to the query I was running.
Should I join that table?

Why don't you have a table of unique ClmNum values? I expect you could get by
without one by using a query:

SELECT dbo_Diary.ClmNum
FROM dbo_Diary
GROUP BY dbo_Diary.ClmNum;

--
Duane Hookom
Microsoft Access MVP

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[Nathan]

No luck. I attached the Case table, and tried it as a left and right join -
didn't work either way.

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Case.CaseID
FROM dbo_Diary RIGHT JOIN dbo_Case ON dbo_Diary.ClmNum = dbo_Case.CaseID
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Case.CaseID
HAVING (((dbo_Case.CaseID)=[Forms]![frmUpdateCaseentry]![CaseID]));

My initial thought was to do an LEFT or RIGHT JOIN but you stated "I have no
sceond table, so an outer join won't do the trick for me". Now apparently
you have a table to join to that selects all records from the ClmNum table.
You may have to change the Having to a Where clause and go against the ClmNum
table rather than the dbo_Diary table.
--
Duane Hookom
Microsoft Access MVP

I do have such a table, but it wasn't germane to the query I was running.
Should I join that table?

Why don't you have a table of unique ClmNum values? I expect you could get by
without one by using a query:

SELECT dbo_Diary.ClmNum
FROM dbo_Diary
GROUP BY dbo_Diary.ClmNum;

--
Duane Hookom
Microsoft Access MVP


:

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[KenSheridan via AccessMonster.com]

Have you tried my suggested solution?

Ken Sheridan
Stafford, England

No luck. I attached the Case table, and tried it as a left and right join -
didn't work either way.

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Case.CaseID
FROM dbo_Diary RIGHT JOIN dbo_Case ON dbo_Diary.ClmNum = dbo_Case.CaseID
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Case.CaseID
HAVING (((dbo_Case.CaseID)=[Forms]![frmUpdateCaseentry]![CaseID]));

My initial thought was to do an LEFT or RIGHT JOIN but you stated "I have no
sceond table, so an outer join won't do the trick for me". Now apparently

[quoted text clipped - 22 lines]

GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));

 

[vanderghast]

Save the actual query as, say, q1. Then, make a second query:


SELECT TOP 1 a.clmNum, NZ(b.CountOfDiaryID, 0)
FROM dbo_diary AS a LEFT JOIN q1
ON a.clmNum = q1.clmNum
WHERE a.clmNum = FORMS!frmUpdateCaseentry!caseID


That should return what you expected. You can replace TOP 1 with DISTINCT,
if you wish, here.


Vanderghast, Access MVP

 

[Nathan]

Aha, your first option below seems to work well. I think I'll go with that.
Thanks so much!

There is a logical inconsistency in what you are currently asking the query
to do, in that you are restricting the results on two other columns, which
might mean that there are no rows with those values and the value of the
grouped column by which you are also restricting the query. Consequently no
row would be returned in which to include the zero count.

One way to do it would be to return the distinct value of the parameter in an
outer query, and the count of the rows which match the parameter value and
the other criteria in a subquery:

SELECT DISTINCT
[Forms]![frmUpdateCaseentry]![CaseID] AS ClmNum,
(SELECT COUNT(*)
FROM dbo_Diary
WHERE ClmNum = [Forms]![frmUpdateCaseentry]![CaseID]
AND Dtype = "Subro"
AND Dcomplete=0) AS DiaryCount
FROM dbo_Diary;

For convenience the outer query uses the same table as the subquery here, but
it could in fact use any table, so you might like to create a table, named
Dummy say, with one column and one row of an arbitrary value, and use that
table in the outer query, which would mean you could then dispense with the
DISTINCT option as the outer query can only return one row

If you did not want to restrict the query by the parameter, but return all
ClmNum values, you could of course correlate the subquery with the outer
query on the ClmNum column:

SELECT DISTINCT ClmNum,
(SELECT COUNT(*)
FROM dbo_Diary As D2
WHERE D2.ClmNum = D1.ClmNum
AND Dtype = "Subro"
AND Dcomplete=0) AS DiaryCount
FROM dbo_Diary AS D1;

In this case the outer query and subquery must use the same table of course,
differentiating them with aliases D1 and D2.

Ken Sheridan
Stafford, England

I need to know when a specfic count is zero. However, when I use the 'count'
function, if its zero, I simply don't get anything. I have no sceond table,
so an outer join won't do the trick for me. my SQL is below. Thanks for
your help!

SELECT Count(dbo_Diary.DiaryID) AS CountOfDiaryID, dbo_Diary.ClmNum
FROM dbo_Diary
WHERE (((dbo_Diary.Dtype)="Subro") AND ((dbo_Diary.Dcomplete)=0))
GROUP BY dbo_Diary.ClmNum
HAVING (((dbo_Diary.ClmNum)=[Forms]![frmUpdateCaseentry]![CaseID]));