Nz does not work in a query with "Count"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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];
Click to expand...


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]
 
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];
Click to expand...


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]
Click to expand...
 
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];
Click to expand...


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]
Click to expand...
Click to expand...
 
Back
Top