counting question part 2

[C White]

A little while back I had a question about counting records and my
example was something like this:

in my database table i have the following records

john
ed
john
rick
tom
ed
john

and i wanted to know how to count the records so i could display it as

3 john
2 ed
1 rick
1 tom

my end solution, with help from the newsgroup readers was:

select Name, count(Name) AS namecount from Table group by Name

now i want to take it a step further

the table looks like this:

john yes
ed no
john no
rick no
tom yes
ed yes
john no

and i would like to display it this way using asp

3 john 2 no
2 ed 1 no
1 rick 1 no
1 tom 0 no

the yes/no is stored as plain text and I know that if i just want to
count the number of times in total it appears i would do something like

select, count(*) AS answer from Table WHERE answer='no'

and i would get something like

3 no total

but how do i combine what i know to get it to display:

3 john 2 no
2 ed 1 no
1 rick 1 no
1 tom 0 no

thanks

 

[Bob Barrows [MVP]]

A little while back I had a question about counting records and my
example was something like this:

in my database table i have the following records

john
ed
john
rick
tom
ed
john

and i wanted to know how to count the records so i could display it as

3 john
2 ed
1 rick
1 tom

my end solution, with help from the newsgroup readers was:

select Name, count(Name) AS namecount from Table group by Name

now i want to take it a step further

the table looks like this:

john yes
ed no
john no
rick no
tom yes
ed yes
john no

and i would like to display it this way using asp

3 john 2 no
2 ed 1 no
1 rick 1 no
1 tom 0 no

the yes/no is stored as plain text and I know that if i just want to
count the number of times in total it appears i would do something
like
select, count(*) AS answer from Table WHERE answer='no'

and i would get something like

3 no total

but how do i combine what i know to get it to display:

3 john 2 no
2 ed 1 no
1 rick 1 no
1 tom 0 no

thanks

You did not mention what type and version of database you are using* so it
is difficult to get specific. Here is a solution that will work in Access:

select Name, count(Name) AS namecount,
SUM(Iif(answer=0,1,0) As answercount
from Table group by Name

Bob Barrows
*Please remember to tell us what database you are using so we don't waste
time with irrelevant solutions

 

[C White]

sorry... I am using an access 2000 database

I tried your example and it gives me an error on this line:

SUM (Iif(answer=0,1,0) As answercount

so i change it to:

SUM If(answer=0,1,0) As answercount

which still gives a missing operator error, however this does give me
something to work with, in the meantime if i come up with an answer i'll
let you know, but any more hints/suggestions are appreciated

thanks

 

[Bob Barrows [MVP]]

sorry... I am using an access 2000 database

I tried your example and it gives me an error on this line:

SUM (Iif(answer=0,1,0) As answercount

What error??? It works fine in my database

so i change it to:

SUM If(answer=0,1,0) As answercount

obviously that won't work. "if" is a VBA keyword, not a function. The
function is "iif"

 

[Bob Barrows [MVP]]

Oh wait! I left out the closing parenthesis. It should be
SUM (Iif(answer=0,1,0)) As answercount

Bob Barrows

 

[C White]

thanks for cluing me in on "Iif"

I added the closing bracket and the error I get is:

Data type mismatch in criteria expression.

could it be due to the fact that the answer field in my table is text?

 

[Bob Barrows [MVP]]

thanks for cluing me in on "Iif"

I added the closing bracket and the error I get is:

Data type mismatch in criteria expression.

could it be due to the fact that the answer field in my table is text?

Umm, yes. I was under the impression that it was a boolean (Yes/No) field.

Just change it to:

SUM (Iif(answer='No',1,0)) As answercount


Bob Barrows

 

[C White]

Great, that worked

Thanks a lot

Your answers also led me to do a bit or reading about Iif as well