When count function doesn't get a hit, there is no entry - need a zero

[cikVOU18]

We have various vulnerability scans that send out a CSV file upon completion. The various sites are each put into a separate table, and then a query is run to determine how many vulnerabilities are present for length of time. For example, the following simply calculated whether a vulnerability has been present for 0 to 30 days, while other queries look at other lengths of time, e.g. < 30 days, 31-90 days, 91-180 days, etc. A typical query is as follows:

SELECT Count([Vulnerability Info].VulnPresentFor) AS [Average Time to Remediate < 30 days], [Vulnerability Info].Risk AS Risk
FROM [Vulnerability Info]
WHERE ((([Vulnerability Info].[VulnPresentFor])<=30))
GROUP BY [Vulnerability Info].Risk;

I need 4 rows for Critical, High, Medium, and Low. The problem is that if COUNT doesn't find anything for any of these, there is no entry. The result is that there may only be 2 or 3 rows instead of all 4.

Can anyone suggest what I might try to fix this? I've search Google for a couple hours and found the "nz" function, but I don't see how that applies in this case.

Thank you.

 

[ahmed_optom]

you need to do something like this

if nz(your current null expression) then
set your value to "0" rather then null
else
end if

 

[arnelgp]

you may use SELECT DISTINCT:

select distinct [risk], 
    (select count("1") from [vulnerability info] as t1 
      where t1=[risk] = [vulnerability info].[risk] and t1.VulnPresentFor <= 30) 
      as [Average Time to Remediate < 30 days]
from [vulnerability info];

 

[cikVOU18]

Thanks for the replies. arnelgp, I'm trying to work through your suggestion, but I'm having a problem understanding "where t1=[risk] = [vulnerability info].[risk]" on the third line. Do you mind elaborating? Thanks

 

[arnelgp]

sorry, its :

t1.[risk]=[vulnerability info].[risk]

 

[cikVOU18]

Thanks, arnelgp. I figured that's what it was, but when I make this change, I don't get a result (the run won't stop) and I have to forcibly close Access and restart. I'm pretty new to this, but it seems reasonable that I should be able to run what's in the parenthesis by itself, something like the following, correct:

select count("1") from [vulnerability info] as t1
where t1.[risk] = [vulnerability info].[risk] and t1.VulnPresentFor <= 30
as [Average Time to Remediate < 30 days];

This seems reasonable, but I can't get this to work either (syntax error (missing operator)). I know I'm doing something wrong here, but I'll continue working on this.

Update: The above query runs when I remove "where t1=[risk] = [vulnerability info].[risk]", but LOW, MEDIUM, HIGH, and CRITICAL counters are all identical.

 

[arnelgp]

if there are only 4 Risks involve,
you can manually do the counting by using UNION query:

SELECT TOP 1 1 AS ID, "LOW" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="LOW" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
UNION
SELECT TOP 1 2 AS ID, "MEDIUM" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="MEDIUM" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
UNION
SELECT TOP 1 3 AS ID, "HIGH" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="HIGH" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
UNION
SELECT TOP 1 4 AS ID, "CRITICAL" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="CRITICAL" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]

 

[cikVOU18]

It's working. Thanks for all your help!

 

[Pat Hartman]

I would have used a crosstab. It would be more efficient on a large set of data.