Solved

access 2003 - Count function

Posted on 2008-06-12
28
1,001 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
[X]
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
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

631 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