Nz does not work in a query with "Count"

K

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!
 
M

Marshall Barton

KarenTheQA said:
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]
 
K

KarenTheQA

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

Marshall Barton said:
KarenTheQA said:
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]
 
D

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
--

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

Marshall Barton said:
KarenTheQA said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top