Nz does not work in a query with "Count"

[KarenTheQA]

I am trying to force zeros for null values using Nz, but it is not working in
the instance below. If a physician has no data to be counted, then his name
does not appear in the query result. I want to see the physician name with a
count of zero in the result.

SELECT [HTN Data 2004-5].[PCP Last Name], Nz(Count([HTN Data 2004-5].[PCP
Last Name])) AS [CountOfPCP Last Name]
FROM [HTN Data 2004-5]
WHERE ((([HTN Data 2004-5].[Latest PCP Visit Date]) Is Not Null))
GROUP BY [HTN Data 2004-5].[PCP Last Name]
ORDER BY [HTN Data 2004-5].[PCP Last Name];

Any help would be appreciated. Thank you!

 

[Marshall Barton]

I am trying to force zeros for null values using Nz, but it is not working in
the instance below. If a physician has no data to be counted, then his name
does not appear in the query result. I want to see the physician name with a
count of zero in the result.

SELECT [HTN Data 2004-5].[PCP Last Name], Nz(Count([HTN Data 2004-5].[PCP
Last Name])) AS [CountOfPCP Last Name]
FROM [HTN Data 2004-5]
WHERE ((([HTN Data 2004-5].[Latest PCP Visit Date]) Is Not Null))
GROUP BY [HTN Data 2004-5].[PCP Last Name]
ORDER BY [HTN Data 2004-5].[PCP Last Name];

How do you know a physician exists if there's no data??

If you have all the data, including all the physicians in
one table (sounds bad to me), then just drop the WHERE
clause??

If you have a master list of physicians in a separate
physicians table, then Join that table in the query:

SELECT physicians.[PCP Last Name],
Count(*) AS [CountOfPCP Last Name]
FROM physicians LEFT JOIN [HTN Data 2004-5]
ON physicians.PK = [HTN Data 2004-5].FK
WHERE [HTN Data 2004-5].[Latest PCP Visit Date] Is Not Null
GROUP BY physicians.[PCP Last Name]
ORDER BY physicians.[PCP Last Name]

 

[KarenTheQA]

Yes, I have a separate physician table (LEFT JOIN). Unfortunately, I could
not get your method to work.

I am trying to force zeros for null values using Nz, but it is not working in
the instance below. If a physician has no data to be counted, then his name
does not appear in the query result. I want to see the physician name with a
count of zero in the result.

SELECT [HTN Data 2004-5].[PCP Last Name], Nz(Count([HTN Data 2004-5].[PCP
Last Name])) AS [CountOfPCP Last Name]
FROM [HTN Data 2004-5]
WHERE ((([HTN Data 2004-5].[Latest PCP Visit Date]) Is Not Null))
GROUP BY [HTN Data 2004-5].[PCP Last Name]
ORDER BY [HTN Data 2004-5].[PCP Last Name];

How do you know a physician exists if there's no data??

If you have all the data, including all the physicians in
one table (sounds bad to me), then just drop the WHERE
clause??

If you have a master list of physicians in a separate
physicians table, then Join that table in the query:

SELECT physicians.[PCP Last Name],
Count(*) AS [CountOfPCP Last Name]
FROM physicians LEFT JOIN [HTN Data 2004-5]
ON physicians.PK = [HTN Data 2004-5].FK
WHERE [HTN Data 2004-5].[Latest PCP Visit Date] Is Not Null
GROUP BY physicians.[PCP Last Name]
ORDER BY physicians.[PCP Last Name]

 

[Duane Hookom]

We can't see any LEFT JOIN in your sql view? You also have a criteria of Is
Not Null so I expect the LEFT JOIN wouldn't do much good.

--
Duane Hookom
MS Access MVP
--

Yes, I have a separate physician table (LEFT JOIN). Unfortunately, I could
not get your method to work.

I am trying to force zeros for null values using Nz, but it is not
working in
the instance below. If a physician has no data to be counted, then his
name
does not appear in the query result. I want to see the physician name
with a
count of zero in the result.

SELECT [HTN Data 2004-5].[PCP Last Name], Nz(Count([HTN Data
2004-5].[PCP
Last Name])) AS [CountOfPCP Last Name]
FROM [HTN Data 2004-5]
WHERE ((([HTN Data 2004-5].[Latest PCP Visit Date]) Is Not Null))
GROUP BY [HTN Data 2004-5].[PCP Last Name]
ORDER BY [HTN Data 2004-5].[PCP Last Name];

How do you know a physician exists if there's no data??

If you have all the data, including all the physicians in
one table (sounds bad to me), then just drop the WHERE
clause??

If you have a master list of physicians in a separate
physicians table, then Join that table in the query:

SELECT physicians.[PCP Last Name],
Count(*) AS [CountOfPCP Last Name]
FROM physicians LEFT JOIN [HTN Data 2004-5]
ON physicians.PK = [HTN Data 2004-5].FK
WHERE [HTN Data 2004-5].[Latest PCP Visit Date] Is Not Null
GROUP BY physicians.[PCP Last Name]
ORDER BY physicians.[PCP Last Name]