COUNTING YES/NO FIELDS

[Andrew UK]

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.

[Arno R]

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.

[Bob Quintal]

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

[HSalim]

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.

[Andrew UK]

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

Andrew

[Andrew UK]

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

[Trevor Best]

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.

[HSalim]

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.