426,107 Members | 1,323 Online
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
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" 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 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" 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" wrote: Andrew,Paste this code in the SQL view of a query (substituting your column andtable names ofcourse) and run itSELECT 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 NoCFROM 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" wrote in message news:vt********************************@4ax.com... On Thu, 25 Sep 2003 03:49:23 GMT in comp.databases.ms-access, "HSalim" wrote:Andrew,Paste this code in the SQL view of a query (substituting your column andtable names ofcourse) and run itSELECT 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 NoCFROM 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.