Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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;
0
Pdeters
Asked:
Pdeters
  • 6
  • 4
  • 2
1 Solution
 
Missus Miss_SellaneusCommented:
SELECT Table2.Answer, SUM(IIF(Table2.Answer = "yes",1,0)) AS [Count]
FROM Table2
GROUP BY Table2.Answer;
0
 
Missus Miss_SellaneusCommented:
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;
0
 
PdetersAuthor Commented:
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",1,0)) AS CountYes, Sum(IIf(Table1.Answer="no",1,0)) AS CountNo
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
PdetersAuthor Commented:
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


0
 
PdetersAuthor Commented:
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.
0
 
PdetersAuthor Commented:
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
0
 
Missus Miss_SellaneusCommented:
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
0
 
Missus Miss_SellaneusCommented:
I created my own table and tested your original query. It works here. I'm using Access 97.
0
 
PdetersAuthor Commented:
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
0
 
PdetersAuthor Commented:
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
0
 
Jeffrey CoachmanCommented:
Pdeters,

Try this

You can cheat a little and subtract the number Yes values from the total number of Yes/No Fields to get the count of the No values.
(See my query)

JeffCoachman
Access-EEQ23477771SummarizeMulti.mdb
0
 
Jeffrey CoachmanCommented:
Pdeters,

But the Report should display what you asked for.

Jeff
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now