display a count in a query when null

[Guest]

Hi, I would like to know how I can include null values in my count query. I
have 8 different categories and each have characteristics which I would like
a total of, but when I run the count query and the category has no
characteristics, it does not appear in the query at all. I would like to see
this category included with all others with a zero next to it. Thanks.

 

[Lynn Trapp]

Select Count(NZ([FieldWithNulls],"N/A"))
From YourTable
Group By SomeField;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Thank you for your reply. I regret to say that I don't understand what I do
with that text. I entered it into SQL but no luck. Could you possibly expand
a little? Thanks.

Select Count(NZ([FieldWithNulls],"N/A"))
From YourTable
Group By SomeField;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Hi, I would like to know how I can include null values in my count query.
I
have 8 different categories and each have characteristics which I would
like
a total of, but when I run the count query and the category has no
characteristics, it does not appear in the query at all. I would like to
see
this category included with all others with a zero next to it. Thanks.

 

[Lynn Trapp]

Could you post your table structure and the SQL that you tried before I sent
that?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Thank you for your reply. I regret to say that I don't understand what I
do
with that text. I entered it into SQL but no luck. Could you possibly
expand
a little? Thanks.

Select Count(NZ([FieldWithNulls],"N/A"))
From YourTable
Group By SomeField;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Hi, I would like to know how I can include null values in my count
query.
I
have 8 different categories and each have characteristics which I would
like
a total of, but when I run the count query and the category has no
characteristics, it does not appear in the query at all. I would like
to
see
this category included with all others with a zero next to it. Thanks.

 

[Guest]

If I understand you correctly you'll need to join the Categories table to the
table which references it, i.e. the one which records the characteristics, in
a LEFT OUTER JOIN so that all categories are returned, e.g.

SELECT Categories.Category, COUNT(MyID) AS CountPerCategory
FROM Categories LEFT JOIN MyTable ON Categories.Category = MyTable.Category
GROUP BY Categories.Category;

where MyTable is the referencing table and MyID is its primary key.