access 2003 - Count function

I have an Access 2003 db and I want to create a report or form that does the following.

Reach record has about 15 fields so it should total 15 yes and no's per record

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;

I have also tried this and it just counts the first field in the record that has yes/no and goes on to the next record.

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;

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23477771.html

any help is appreciated - i am stuck
PdetersAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
frankyteeConnect With a Mentor Commented:
if you only want YES, NO, n/a ONCE per record
SELECT [Name],
SUM(IIF(field2 = "yes",1,0))
+ SUM(IIF(field3 = "yes",1,0))
+ etc...
+ SUM(IIF(field15 = "yes",1,0))  as CountYES,

SUM(IIF(field2 = "no",1,0))
+ SUM(IIF(field3 = "no",1,0))
+ etc...
+ SUM(IIF(field15 = "no",1,0))  as CountNO,

SUM(IIF(field2 = "n/a",1,0))
+ SUM(IIF(field3 = "n/a",1,0))
+ etc...
+ SUM(IIF(field15 = "n/a",1,0))  as CountNA

FROM Table2
GROUP BY [name]
0
 
peter57rCommented:
Assuming all answers MUST be completed then you can use :
TotalYes:Abs(field2+field3+field4......+field16)
and
TotalNo:15-(Abs(field2+field3+field4......+field16) )
0
 
frankyteeCommented:
assuming you want to group by field1 (name) and field2 is called "answer" and is a yes/no field (ie true/false)

SELECT Table2.Name
SUM(IIF(Table2.Answer = -1,1,0)) AS [CountYes],
SUM(IIF(Table2.Answer = 0,1,0)) AS [CountNo],
FROM Table2
GROUP BY Table2.Name
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
PdetersAuthor Commented:
How do I get it to count  the field 3 field 4 etc.
0
 
PdetersAuthor Commented:
When I do this it jsut gives me the count for the first yes/no field  in the record - i have about 15 fiedls on each record with a yes/no and want to total for the entire record

SELECT Table2.Name
SUM(IIF(Table2.Answer = -1,1,0)) AS [CountYes],
SUM(IIF(Table2.Answer = 0,1,0)) AS [CountNo],
FROM Table2
GROUP BY Table2.Name
0
 
frankyteeCommented:
are you trying to add field2 + field2 + field4 etc?
0
 
PdetersAuthor Commented:
Sorry my mistake
When you say Select Table2.Name what should I be putting in Name
0
 
frankyteeCommented:
select [name],
(-1*field2) + (-1*field3) + (-1*field4) ...... etc as CountYes,
iif(field2=0,1,0) +
iif(field3=0,1,0) +
iif(field4=0,1,0) +
...... etc as CountNo
from table
0
 
PdetersAuthor Commented:
I want to add
are you trying to add field2 + field2 + field4 etc?
How do I do that?
0
 
PdetersAuthor Commented:
Why do I have [name] - it is popup up wehn i run the query for an input
0
 
frankyteeCommented:
i thought your field1 was called "name"
make it
select field1, .... etc
0
 
PdetersAuthor Commented:
When i try tihs i get "The SELECT statement includes reserve word or an argument name that is misspelled or missing, or th epunctuation is incorrect. I can't see whatI have typed wrong. It hangs up on the FROM

SELECT Table2.Name,
SUM(IIF(Table2.Answer = -1,1,0)) AS [CountYes],
SUM(IIF(Table2.Answer = 0,1,0)) AS [CountNo],
FROM Table2
GROUP BY Table2;
0
 
PdetersAuthor Commented:
I change the first field to name. It is getting hung up on the FROM

All the fields except the first field have text in them as answers to questoins - the test is yes or no
0
 
frankyteeCommented:
i made a typo, there should be no comma after CountNo.

SELECT Table2.Name
SUM(IIF(Table2.Answer = -1,1,0)) AS [CountYes],
SUM(IIF(Table2.Answer = 0,1,0)) AS [CountNo]
FROM Table2
GROUP BY Table2.Name

however if "name" is not an actual field then you should replace it with field1 or whatever the field is.
also this sums the yes/no across ROWS rather than columns. if you  want to sum the columns (field2, field3 etc) then use my previous post, ie
select field1,
(-1*field2) + (-1*field3) + (-1*field4) ...... etc as CountYes,
iif(field2=0,1,0) +
iif(field3=0,1,0) +
iif(field4=0,1,0) +
...... etc as CountNo
from table
0
 
PdetersAuthor Commented:
i put a Comma after countNo and it still hung up on FROM
0
 
PdetersAuthor Commented:
Yes it is across in rows not colums
0
 
frankyteeCommented:
>i put a Comma after countNo and it still hung up on FROM
there should be NO comma.
post the exact field names and table names and are the fields actual yes/no fields (ie true/false) or text fields with values "yes" and "no" as there is a big difference between the 2 field types.
0
 
PdetersAuthor Commented:
Table2

FIeld names
Name       field1             field2           field3

All are text fields
0
 
PdetersAuthor Commented:
i took out the comma and it tells me data mismatch.
Would it be better to change the field types.
I need to have yes, no, n/a in the drop down box for selection
0
 
frankyteeCommented:
i'm even more confused.
so you have a field called name and also a field calld field1?
is field1 a "yes"/"no" field (the same as field2,3 , 4 etc) or is it something else?
0
 
PdetersAuthor Commented:
Sorry

I have a table called table2
I have a field called Name, FIeld2, FIeld3, Field4
each field is a text field
field2, field3, field4 are all text fields with a dropdown combo box to choose Yes, No or N./A
0
 
PdetersAuthor Commented:
I want to run a reoprt /query that shows how many of each answer there is for each reocrd

Record 1 has 1 yes and 2 nos and 1 n/s
Record 2 has 2 yes and 1 no an 0 n/a
0
 
frankyteeCommented:
ok, now we're clear, leave all your fields as text

SELECT [Name],
SUM(IIF(field2 = "yes",1,0)) AS field2CountYes,
SUM(IIF(field2 = "no",1,0)) AS field2CountNo,

SUM(IIF(field3 = "yes",1,0)) AS field3CountYes,
SUM(IIF(field3 = "no",1,0)) AS field3CountNo,
...etc
SUM(IIF(field15 = "yes",1,0)) AS field15CountYes,
SUM(IIF(field15 = "no",1,0)) AS field15CountNo
FROM Table2
GROUP BY [name]
0
 
frankyteeCommented:
just saw your last post after i posted mine. so you now want to sum across columns as well now,
ie add field2 + field3 + field4 etc?
0
 
PdetersAuthor Commented:
Ok - I have to go now but will try this and check back and let you know in about 21/2 hours
Thank you so much for you help!! It is very much apprciated.
0
 
PdetersAuthor Commented:
Do I do this

just saw your last post after i posted mine. so you now want to sum across columns as well now,
ie add field2 + field3 + field4 etc?

After I do  this
SELECT [Name],
SUM(IIF(field2 = "yes",1,0)) AS field2CountYes,
SUM(IIF(field2 = "no",1,0)) AS field2CountNo,

0
 
PdetersAuthor Commented:
I want all the yes and nos per record - if there are 10 yes and 5 no that is what I want to show for that record
will check back here and let you know in couple of hours
0
 
PdetersAuthor Commented:
thank you for your patients it works great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.