Count zeros

[john]

SELECT DISTINCT Ap2.test1, Count(Ap2.test1 ) AS AantalVantest1
FROM Ap2
GROUP BY Ap2.test1;

gives me a record count per unique value of test1 field. How do I include
the count of all the records without a value? The table resulting from the
query just shows an empty value with 0 as the count which is not correct.

thanks,
john

[Bob Quintal]

"john" <jo**@test.comw rote in
news:xt******** ************@ca sema.nl:

SELECT DISTINCT Ap2.test1, Count(Ap2.test1 ) AS AantalVantest1
FROM Ap2
GROUP BY Ap2.test1;

gives me a record count per unique value of test1 field. How
do I include the count of all the records without a value? The
table resulting from the query just shows an empty value with
0 as the count which is not correct.

thanks,
john

Your problem is that instances of ap2.test1 are null, which access
properly refuses to count. Change the count() to count something
which is not null. Use count("X") AS AantalVantest1 instead.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[john]

I don't really understand how... but it works ;-)
Thanks,
john

"Bob Quintal" <rq******@sPAmp atico.caschreef in bericht
news:Xn******** **************@ 66.150.105.47.. .

"john" <jo**@test.comw rote in
news:xt******** ************@ca sema.nl:

>SELECT DISTINCT Ap2.test1, Count(Ap2.test1 ) AS AantalVantest1
FROM Ap2
GROUP BY Ap2.test1;

gives me a record count per unique value of test1 field. How
do I include the count of all the records without a value? The
table resulting from the query just shows an empty value with
0 as the count which is not correct.

thanks,
john

Your problem is that instances of ap2.test1 are null, which access
properly refuses to count. Change the count() to count something
which is not null. Use count("X") AS AantalVantest1 instead.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"john" <jo**@test.comw rote in
news:Cd******** ************@ca sema.nl:

I don't really understand how... but it works ;-)
Thanks,
john

Allen Browne, who is a regular responder here, has some pretty
good information on nulls

The Query Lost My Records! (Nulls)
http://allenbrowne.com/casu-02.html

Common errors with Null
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
his whole tips page is worth bookmarking.
http://allenbrowne.com/tips.html

"Bob Quintal" <rq******@sPAmp atico.caschreef in bericht
news:Xn******** **************@ 66.150.105.47.. .

>"john" <jo**@test.comw rote in
news:xt******* *************@c asema.nl:

>>SELECT DISTINCT Ap2.test1, Count(Ap2.test1 ) AS
AantalVantest 1 FROM Ap2
GROUP BY Ap2.test1;

gives me a record count per unique value of test1 field. How
do I include the count of all the records without a value?
The table resulting from the query just shows an empty value
with 0 as the count which is not correct.

thanks,
john

Your problem is that instances of ap2.test1 are null, which
access properly refuses to count. Change the count() to count
something which is not null. Use count("X") AS AantalVantest1
instead.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com