Handling Null values returned from a query

[KavJack]

KavJack (Programmer) Jan 7, 2002
The following code returns a year and a count.
SELECT CUPALL2.YR, IIf (
IsNull (Count(*)) ,0, Count(*) )
FROM CUPALL2
WHERE CUPALL2.YR>1944 AND CUPALL2.RD="R3" AND
CUPALL2.RP Is Null AND
( (CUPALL2.L1="N&quot OR (CUPALL2.L2="N&quot )
GROUP BY CUPALL2.YR;
However, for year 1951, there are no values. My SQL returns
1946 1
1947 1
1948 2
1949 1
1950 3
1952 2
1953 2 etc.
But for 1950 there is no line. How do I get the code to return "1951 0" ? I have checked back answers on this forum, but none seem to work for me. What am I doing wrong ?

 

[tlbroadbent]

Here is one possibility.

Create a table (Years) with one column (YR) and insert all the year values possible into this table. Then create a JOIN query as follows. This will select all YR from the Years table and count matching records in the CUPALL2 table. When the count is zero, the RecCnt value will return zero.
[tt]
SELECT y.YR, RecCNt=Count(c.*)
FROM Years y Left Join CUPALL2 c
ON y.YR=c.YR
WHERE c.YR>1944
AND c.RD="R3"
AND c.RP Is Null
AND (c.L1="N" OR c.L2="N&quot
GROUP BY y.YR;[/tt] Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[RickSpr]

Presumably, you have selected no rows for 1951, so you shouldn't be surprised that there is nothing for it to group, and therefore no row in the result. You also have no rows, presumably, for 1776. You didn't want to see "1776 0" in your results, did you?

I assume you want a result row for every unique year that appears in CUPALL2, whether or not any rows are selected by your WHERE clause. That is, you want the same rows that would come from:

    SELECT DISTINCT YR FROM CUPALL2

only you want the count of selected rows for each of these.
Go ahead and create the above query, saving it as, say, YEARS. Then modify your query to this:

    SELECT YR, Count(*) AS CNT
    FROM CUPALL2
    WHERE CUPALL2.YR>1944 AND  CUPALL2.RD="R3" AND
     CUPALL2.RP Is Null AND 
     ( (CUPALL2.L1="N")  OR (CUPALL2.L2="N") )
    GROUP BY CUPALL2.YR;

There's no need for the IIF() function in this query, because Count(*) can never be null; years that have no selected rows won't appear in it at all (that was your original problem). Save this query as, for example, YRCOUNTS.

Finally, create another query that joins YRCOUNTS with YEARS:

    SELECT YEARS.YR, IIF(IsNull(CNT), 0, CNT)
    FROM YEARS LEFT JOIN YRCOUNTS ON YEARS.YR = YRCOUNTS.YR
    ORDER BY YEARS.YR

This query contains the results you want. By using a left join, you'll introduce the null counts you expected for years that have no rows selected in YRCOUNTS. Thus, you now need the IIF() to convert those nulls to 0. The difference here is that you now have a row (in YEARS) for every year in CUPALL2, so you're not asking Access to create one out of thin air any more.

(I didn't test this, but you seem to be competent with SQL and should be able to fix any minor problems with it.) Rick Sprague

 

[KavJack]

Thanks for your very prompt replies.
The second suggestion worked like a charm the first time as described.
The first suggestion wouldn't compile as described. I don't know if this is a different or newer version of Access. I am using Access 97 with Windows 98.
When I tried to save the SQL I obtained the message
Syntax Error (missing operator) in query expression RecCnt=Count(c.*)
When I replaced RecCnt=Count(c.*) by (C.HT) where HT is another field name the SQL would save and run but it produced the same result as before, i.e. no row for Year 1951 with a zero value.