[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

access 2003 - Count function

Posted on 2008-06-12
28
Medium Priority
?
1,020 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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 2000 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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

591 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