Forcing Null

[dannylam4]

So I have a table that does a count on ratings in a certain category. The problem is that I want a number to appear for each of the three categories, even when the count returns nothing. In other words, we have 3 rating types, "poor, good, and great," and I need a result for it, even if no one gave the category a poor rating. Now I figured that if I created a table (tblCateg) that just held the three categories (sCatDesc) and did a join on the other table (Ratings) with the column (IN_Category) that I needed, it would force the query to make all 3 ratings appear and a count for each. Unfortunately, this didn't happen. All that appeared was
---------
Great 17
Good 7
---------

I figure the query is just seeing the count for the Poor rating as a Null rather than a 0; anyone know how I can force it to see it as a 0 instead?

Here's the query I'm using:

SELECT tblCateg.sCatDesc, Count(Ratings.IN_Category) AS CountOfIN_Category
FROM tblCateg LEFT JOIN Ratings ON tblCateg.sCatDesc = Ratings.IN_Category
WHERE (((Ratings.RatingDT) Between #1/1/2007# And #3/31/2007#))
GROUP BY tblCateg.sCatDesc;

 

[Dennisk]

use the NullToZero function nz()

 

[dannylam4]

in here: nz(Count(Rating.IN_Category)) AS CountOfIN_Category ? That didn't seem to work either... so I guess it's not looking it as null? It just doesn't want to display 0's?

 

[Banana]

Ah, I did ask same question so long ago.

Basically you need two query.

First, do a count of everything you need.

Now, create a second query that has the first query and the table with rows where you want to group by. Join them again so that the all records from table are included. Use Nz() for the query's count.

The reason why you can't do this in single query is because relatively speaking there's no such nothing as "null counts". Access simply discards whatever rows that has zero count, so even catching for null won't stop Access from discarding those rows (though it's a must if you are concerned about null propagation). You therefore need to tell Access that you want each row listed, and whereever Access finds a row that has no count match, it's being designated as null, which you now can catch with Nz().

HTH.

 

[dannylam4]

Hm... so if I'm understanding this correctly, have my query in the first post (with join and all) and then create a second query that has the nz() function? If so, I don't think I did it right, haha. Here's the code for the 2nd query:

SELECT nz([Pre Parent].CountOfIN_Category) AS Counter
FROM rating, [Pre Parent] INNER JOIN tblCateg ON [Pre Parent].sCatDesc = tblCateg.sCatDesc;

It just displayed 17 and 7's 186 times.

 

[neileg]

I think Banana is giving you a bit too much information. You were on the right lines at the beginning. The problem is that you have tried to do this all in one query. You would need to add a subquery to make it work. Since I'm a hacker and not a programmer, I do it the long way with two queries. So....
Create an aggregate query that returns your count. Probably going to be
SELECT Ratings.IN_Category, Count(Ratings.IN_Category) AS CountOfIN_Category
FROM Ratings
WHERE (((Ratings.RatingDT) Between #1/1/2007# And #3/31/2007#))
GROUP BY Ratings.IN_Category;

Then create a second query that joins tblCateg with a left join to the first query.

 

[dannylam4]

Cool ^^ It got all 3 categories to appear, though Strength's count is still blank. I wonder if that'll cause problems when attempting to build a graph off it...

--Nope! Works perfectly fine! Thank you veeeery much, haha!

 

[Dennisk]

don't forget to set the default parameter for the nz function

nz(fieldname,0)

 

[dannylam4]

Ahh, ",0" I forgot that part... I guess that makes sense. thanks, haha.

 

[neileg]

Nz() defaults to ",0" if you miss out that parameter, so it depends how much typing you want to do!