Solved

access 2003 - Count function

Posted on 2008-06-12
28
996 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 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
0
Comment
Question by:Pdeters
  • 17
  • 10
28 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 21767880
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21767898
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
 

Author Comment

by:Pdeters
ID: 21767912
How do I get it to count  the field 3 field 4 etc.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Pdeters
ID: 21767928
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21767931
are you trying to add field2 + field2 + field4 etc?
0
 

Author Comment

by:Pdeters
ID: 21767949
Sorry my mistake
When you say Select Table2.Name what should I be putting in Name
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21767956
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
 

Author Comment

by:Pdeters
ID: 21767957
I want to add
are you trying to add field2 + field2 + field4 etc?
How do I do that?
0
 

Author Comment

by:Pdeters
ID: 21767965
Why do I have [name] - it is popup up wehn i run the query for an input
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21767979
i thought your field1 was called "name"
make it
select field1, .... etc
0
 

Author Comment

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

Author Comment

by:Pdeters
ID: 21768039
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21768055
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
 

Author Comment

by:Pdeters
ID: 21768079
i put a Comma after countNo and it still hung up on FROM
0
 

Author Comment

by:Pdeters
ID: 21768081
Yes it is across in rows not colums
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21768093
>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
 

Author Comment

by:Pdeters
ID: 21768114
Table2

FIeld names
Name       field1             field2           field3

All are text fields
0
 

Author Comment

by:Pdeters
ID: 21768151
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21768158
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
 

Author Comment

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

Author Comment

by:Pdeters
ID: 21768215
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21768239
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21768254
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
 

Author Comment

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

Author Comment

by:Pdeters
ID: 21768301
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
 
LVL 19

Accepted Solution

by:
frankytee earned 500 total points
ID: 21768343
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
 

Author Comment

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

Author Closing Comment

by:Pdeters
ID: 31466488
thank you for your patients it works great
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

821 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