Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Count certain fields in a record on what they contain - Access 2003

Posted on 2008-06-11
12
281 Views
Last Modified: 2013-11-28
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
Comment
Question by:Pdeters
  • 6
  • 4
  • 2
12 Comments
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 21764911
SELECT Table2.Answer, SUM(IIF(Table2.Answer = "yes",1,0)) AS [Count]
FROM Table2
GROUP BY Table2.Answer;
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 21764921
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
 

Author Comment

by:Pdeters
ID: 21765851
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:Pdeters
ID: 21765966
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
 

Author Comment

by:Pdeters
ID: 21766043
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
 

Author Comment

by:Pdeters
ID: 21766086
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
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 21766189
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
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 21766201
I created my own table and tested your original query. It works here. I'm using Access 97.
0
 

Author Comment

by:Pdeters
ID: 21767774
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
 

Author Comment

by:Pdeters
ID: 21767813
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 21800195
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21800209
Pdeters,

But the Report should display what you asked for.

Jeff
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question