Solved

access 2003 - Count function

Posted on 2008-06-12
28
988 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
Comment Utility
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
Comment Utility
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
Comment Utility
How do I get it to count  the field 3 field 4 etc.
0
 

Author Comment

by:Pdeters
Comment Utility
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
Comment Utility
are you trying to add field2 + field2 + field4 etc?
0
 

Author Comment

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

Expert Comment

by:frankytee
Comment Utility
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
Comment Utility
I want to add
are you trying to add field2 + field2 + field4 etc?
How do I do that?
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:Pdeters
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i put a Comma after countNo and it still hung up on FROM
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Pdeters
Comment Utility
Yes it is across in rows not colums
0
 
LVL 19

Expert Comment

by:frankytee
Comment Utility
>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
Comment Utility
Table2

FIeld names
Name       field1             field2           field3

All are text fields
0
 

Author Comment

by:Pdeters
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thank you for your patients it works great
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now