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]]

C White wrote:

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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

C White wrote:

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"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

C White wrote:

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

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

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

C White wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[C White]

Great, that worked

Thanks a lot :)

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!