counting a null value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 22 Nov 2005 15:01 GMT

I have a query where I am counting the number of items each group has...for
instance it would return the following:

Group A 20
Group B
Group C 15
Group D 30

The problem is nothing shows up for Group B because there are no values. Is
it possible that when there are no values to default a 0 in the query return
? I'm asking because in subsequent queries I have to do a percent
calculation off of this and it comes back blank instead of 0%

Thanks for reading and for any ideas you can give me.

Reply to this Message

Sunil - 22 Nov 2005 15:20 GMT

hi,

i think the below query will solve ur problem

SELECT nz(count(group_name),0)
FROM TABLE_NAME group by group_id;

u can use NZ function for that...

psl let me know if this helps u...ok

Thanks

With regards

Sunil.T

> I have a query where I am counting the number of items each group has...for
> instance it would return the following:
[quoted text clipped - 10 lines]
>
> Thanks for reading and for any ideas you can give me.

Reply to this Message

Chris - 22 Nov 2005 15:36 GMT

thanks for the reply Sunil...quick question. How do I put that into the
standard query display for Access...that looks like an SQL statement and I'm
not too hot with that.

Would I put "nz(count(group_name),0)" under criteria or field or somewhere
else ?

thanks again

> hi,
>
[quoted text clipped - 27 lines]
> >
> > Thanks for reading and for any ideas you can give me.

Reply to this Message

Chris - 22 Nov 2005 16:02 GMT

ok...ignore the last one, I manipulated it a bit and got it in
there...problem is, if the count is 0 it still returns nothing 8-(

any other ideas ?

thanks

> hi,
>
[quoted text clipped - 27 lines]
> >
> > Thanks for reading and for any ideas you can give me.

Reply to this Message

Sunil - 23 Nov 2005 13:22 GMT

hi,

that query should work..here in my machine its working...u can replace that
nz function with iif( isnull(count(colname))=true,0,count(colname))

thanx

With regds

Sunil.T

> ok...ignore the last one, I manipulated it a bit and got it in
> there...problem is, if the count is 0 it still returns nothing 8-(
[quoted text clipped - 34 lines]
> > >
> > > Thanks for reading and for any ideas you can give me.

Reply to this Message