Pdeters
asked on
Count certain fields in a record on what they contain - Access 2003
I have an Access 2003 db and I want to create a report or form that does the following.
Reach record has about 15.
Field1, field2, field3 etc.
Field1 is Name
FIeld2 is a question with a reposne of Yes, No
Field3 is an other quesitons with a resonse option of yes or no
etc. etc.
I want to be able to sumaries each record as to how many yes answers there are and how many no answers there are.
I tried somethign like this but it did not work - it gave me the amount of reocrds in the the table.
SELECT Table2.Answer, Count(Table2.Answer) AS [Count]
FROM Table2
GROUP BY Table2.Answer;
Reach record has about 15.
Field1, field2, field3 etc.
Field1 is Name
FIeld2 is a question with a reposne of Yes, No
Field3 is an other quesitons with a resonse option of yes or no
etc. etc.
I want to be able to sumaries each record as to how many yes answers there are and how many no answers there are.
I tried somethign like this but it did not work - it gave me the amount of reocrds in the the table.
SELECT Table2.Answer, Count(Table2.Answer) AS [Count]
FROM Table2
GROUP BY Table2.Answer;
Sorry, I didn't count the No's.. try this one:
SELECT Table2.Answer, SUM(IIF(Table2.Answer = "yes",1,0)) AS [CountYes],
SUM(IIF(Table2.Answer = "no",1,0)) AS [CountNo],
FROM Table2
GROUP BY Table2.Answer;
SELECT Table2.Answer, SUM(IIF(Table2.Answer = "yes",1,0)) AS [CountYes],
SUM(IIF(Table2.Answer = "no",1,0)) AS [CountNo],
FROM Table2
GROUP BY Table2.Answer;
ASKER
This is what i am getting in the query
Headings -Answer CountYes CountNo
Row Yes 3 0
This is what I am using
SELECT Table1.answer, Sum(IIf(Table1.Answer="yes
FROM Table1
GROUP BY Table1.answer;
There are 3 records with a total of 6 yes and 2 now
I need it to show 6 yes and 2 no
Headings -Answer CountYes CountNo
Row Yes 3 0
This is what I am using
SELECT Table1.answer, Sum(IIf(Table1.Answer="yes
FROM Table1
GROUP BY Table1.answer;
There are 3 records with a total of 6 yes and 2 now
I need it to show 6 yes and 2 no
ASKER
This is what I want it to show
Headings -Answer Count
Row Yes 6
No 2
or
Headings -Answer Yes tNo
Row Yes 6 2
Headings -Answer Count
Row Yes 6
No 2
or
Headings -Answer Yes tNo
Row Yes 6 2
ASKER
It is only counting the first field that has yes/no as an option .
How can I get it to count all the fields in the record that have yes/no options.
How can I get it to count all the fields in the record that have yes/no options.
ASKER
So for record1 should be 2 yes and 1 no
Record 2 shoulld have 2 yes and 1 no
Record 3 should have 2 yes and 0 no
Record 2 shoulld have 2 yes and 1 no
Record 3 should have 2 yes and 0 no
What exactly does your original query do? You said it gives you the number of records instead of the count. It looks like it should work. Is this what it's giving you?
Answer Count
Yes 6
No 6
Answer Count
Yes 6
No 6
I created my own table and tested your original query. It works here. I'm using Access 97.
ASKER
This is what it is giving me
Headings -Answer CountYes CountNo
Row Yes 3 0
I am using Access 2003
Thisi s what I am looking for
This is what I want it to show
Headings -Answer Count
Row Yes 6
No 2
or
Headings -Answer Yes tNo
Row Yes 6 2
Headings -Answer CountYes CountNo
Row Yes 3 0
I am using Access 2003
Thisi s what I am looking for
This is what I want it to show
Headings -Answer Count
Row Yes 6
No 2
or
Headings -Answer Yes tNo
Row Yes 6 2
ASKER
It is only grabbing the yes and no from the first field in each record that has yes/no - am looking for it tor the yes/no from all the fields
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pdeters,
But the Report should display what you asked for.
Jeff
But the Report should display what you asked for.
Jeff
FROM Table2
GROUP BY Table2.Answer;