By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

COUNTING YES/NO FIELDS

P: n/a
I have a query that returns the following sample data: Surname,
Baptised, Confirmed, Regular Communicant. The last 3 fields are
yes/no type. I want to report the number of "Yes" answers in each
field. I can see it easily enough on a report and add them up
manually, but can't think of a way of wording the query or a
calculated control within the report. Any suggestions, please? Is
there a (Count[fieldname]=Yes) type of construction? By the way, I'm
completely ignorant of VBA. Thanks for any help.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Andrew,
Yes has a value of -1 and No has a value of 0
So you can just Sum all your Yes/No Fields like:
Sum = - (YesNoField1 + YesNoField2 + YesNoField3)

--
Hope this helps
Arno R
"Andrew UK" <an****@aptrainingonline.co.uk> schreef in bericht
news:98**************************@posting.google.c om...
I have a query that returns the following sample data: Surname,
Baptised, Confirmed, Regular Communicant. The last 3 fields are
yes/no type. I want to report the number of "Yes" answers in each
field. I can see it easily enough on a report and add them up
manually, but can't think of a way of wording the query or a
calculated control within the report. Any suggestions, please? Is
there a (Count[fieldname]=Yes) type of construction? By the way, I'm
completely ignorant of VBA. Thanks for any help.

Nov 12 '05 #2

P: n/a
an****@aptrainingonline.co.uk (Andrew UK) wrote in
news:98**************************@posting.google.c om:
I have a query that returns the following sample data:
Surname, Baptised, Confirmed, Regular Communicant. The last 3
fields are yes/no type. I want to report the number of "Yes"
answers in each field. I can see it easily enough on a report
and add them up manually, but can't think of a way of wording
the query or a calculated control within the report. Any
suggestions, please? Is there a (Count[fieldname]=Yes) type
of construction? By the way, I'm completely ignorant of VBA.
Thanks for any help.


Easy as 1,2,3.

Use the query design tool.

1) Once you see your query, just click on the Sigma (aka Funny Z
mark) on the toolbar. A new row will appear on your design grid,
labeled TOTAL. The data for each item on the grid will read "Group
By".
Delete your Surname field if you want to total on the whole
membership, or leave it as group by if you want family totals, and
change (double-click) to "Sum". Double-click again to get "avg" if
you want, or open the dropdown to see all the choices.

Save and run the query. It will return the sum of each column's yes
values with a negative sign in front of it.

2) To get rid of the minus signs, go back to design mode.
Add *-1 to the end of each of the three fields.

Access adds names like EXPR1, EXPR2, EXPR3, and a colon ":" to
separate it from the statement.

3) change each EXPRx to a maningful name, save and run your totals
query.

Bob Q

Nov 12 '05 #3

P: n/a
Andrew,
Paste this code in the SQL view of a query (substituting your column and
table names ofcourse) and run it

SELECT Sum( iif(A = -1,1,0)) as YesA,
Sum( iif(A = 0,1,0)) as NoA,
Sum( iif(B = -1,1,0)) as YesB,
Sum( iif(B = 0,1,0)) as NoB,
Sum( iif(C = -1,1,0)) as YesC,
Sum( iif(C = 0,1,0)) as NoC
FROM Table1
HS
"Andrew UK" <an****@aptrainingonline.co.uk> wrote in message
news:98**************************@posting.google.c om...
I have a query that returns the following sample data: Surname,
Baptised, Confirmed, Regular Communicant. The last 3 fields are
yes/no type. I want to report the number of "Yes" answers in each
field. I can see it easily enough on a report and add them up
manually, but can't think of a way of wording the query or a
calculated control within the report. Any suggestions, please? Is
there a (Count[fieldname]=Yes) type of construction? By the way, I'm
completely ignorant of VBA. Thanks for any help.

Nov 12 '05 #4

P: n/a
Thanks to both Arno and Bob. I'll try your suggestions and let you
know how I get on!

Andrew
Nov 12 '05 #5

P: n/a
OK, I've tried your suggestions. Again, thanks to both of you. I am
so stupid!!! I had remembered that Yes is stored as -1 and No as 0 but
somehow hadn't made the leap. I was trying to Count them (thinking of
them as some kind of text field), when obviously Sum works perfectly!

I'm so pleased to have found this site. It could definitely make life
easier.

Andrew
Nov 12 '05 #6

P: n/a
On Thu, 25 Sep 2003 03:49:23 GMT in comp.databases.ms-access, "HSalim"
<HS****@msn.com> wrote:
Andrew,
Paste this code in the SQL view of a query (substituting your column and
table names ofcourse) and run it

SELECT Sum( iif(A = -1,1,0)) as YesA,
Sum( iif(A = 0,1,0)) as NoA,
Sum( iif(B = -1,1,0)) as YesB,
Sum( iif(B = 0,1,0)) as NoB,
Sum( iif(C = -1,1,0)) as YesC,
Sum( iif(C = 0,1,0)) as NoC
FROM Table1


I haven't tested for speed but the following may work faster
(definately can be typed in faster) than the IIf() functions:

For Access, where Yes is always -1: Sum(a)*-1

For portable code, e.g. maybe upsizing to SQL Server later on where a
Yes will be 1: Sum(Abs(a))

So either the Abs() or the *-1 will net you a 1, to count zeros just
use sum(1 - abs(a)) or Sum(1-a*-1) or simpler Sum(Not A)

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #7

P: n/a
Trevor,
You are right, and it is a neater solution and portable.
Select Abs(Sum(A)) as YesA,
Abs(Sum(Not A)) as NoA,
Abs(Sum(B)) as YesB,
Abs(Sum(Not B)) as NoB,
Abs(Sum(C)) as YesC,
Abs(Sum(Not C)) as NoC
from Table1
"Trevor Best" <bouncer@localhost> wrote in message
news:vt********************************@4ax.com...
On Thu, 25 Sep 2003 03:49:23 GMT in comp.databases.ms-access, "HSalim"
<HS****@msn.com> wrote:
Andrew,
Paste this code in the SQL view of a query (substituting your column and
table names ofcourse) and run it

SELECT Sum( iif(A = -1,1,0)) as YesA,
Sum( iif(A = 0,1,0)) as NoA,
Sum( iif(B = -1,1,0)) as YesB,
Sum( iif(B = 0,1,0)) as NoB,
Sum( iif(C = -1,1,0)) as YesC,
Sum( iif(C = 0,1,0)) as NoC
FROM Table1


I haven't tested for speed but the following may work faster
(definately can be typed in faster) than the IIf() functions:

For Access, where Yes is always -1: Sum(a)*-1

For portable code, e.g. maybe upsizing to SQL Server later on where a
Yes will be 1: Sum(Abs(a))

So either the Abs() or the *-1 will net you a 1, to count zeros just
use sum(1 - abs(a)) or Sum(1-a*-1) or simpler Sum(Not A)

--
A)bort, R)etry, I)nfluence with large hammer.

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.