Solved

Counting Yes/No Fields

Posted on 2000-03-21
20
476 Views
Last Modified: 2008-02-20
I have a field that is defined Yes/No.  I want to put two calculated fields on a report and have one count for the yes and one count for the no.  I have tried this count([fulltime] = True) and it still returned the same count regardless.
0
Comment
Question by:Accessed
  • 4
  • 3
  • 3
  • +7
20 Comments
 
LVL 7

Expert Comment

by:Believer
ID: 2641205
It may depend on where you want the totals to show up.  I think I did something similar to this a long time ago...
If you want the yes/no counts to appear in the report footer, put a subreport in the footer.  The subreport is just based off a query that gets the totals for you.
I created a sample table with the following fields:
  cntFieldID as AutoNumber
  bYesNo as Yes/No

I put a bunch of records in the table, then created this query/SQL to give me counts:
SELECT Table1.bYesNo, Count(Table1.cntFieldID) AS CountOfcntFieldID FROM Table1 GROUP BY Table1.bYesNo;
0
 
LVL 4

Expert Comment

by:abaldwin
ID: 2641256
How bout this

I created a table
one of the fields is called IsOkay   data type Yes/no

On the report in the report footer I placed two unbound text boxs

The control source for these box's are as follows

For yes's =Sum(IIf([isokay]=-1,1,0))

For No's =Sum(IIF([IsOkay] = 0,1,0))

I think your count function using the = sign is going to give you the same answer because there is always something in a yes no field.  -1 = yes and 0 = no

Andy


0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2641260
Hi Accessed.

1. Locate on detail section 2 invisible fields txtYes and txtNo, set for this fields "Control Source" property to:
  =IIf([fldBoolean]=True;1;0)
  =IIf([fldBoolean]=False;1;0)
respectively and "Running Sum" to "Over All".
2. Locate txtCountOfYes and txtCountOfNo in the report footer and set control sources to:
=[txtYes]
=[txtNo]
3. Save, Close and Run your report.

Regards,
Dedushka
0
 
LVL 10

Expert Comment

by:paasky
ID: 2641261
Hello Accessed,

here's another way I use quite often. You can play with other conditions like this too. Set your textfields Control Property like this:

Count Yes/True values:
=SUM(ABS([yes/no]=True))

Count No/False values:
=SUM(ABS([yes/no]=False))

Regards,
Paasky
0
 
LVL 3

Expert Comment

by:tomk120999
ID: 2641436
Hi, Accessed, I kinda like the domain aggregate functions to do this, like below:

The "DCount(expression, domain, criteria)" will do what you want.  Make it the control source for the appropriate text box.  The arguments must be strings (enclosed in quotes), and, in the property sheet, you will need an equals sign (=) in front of the function.  The expression below will give you the True count (substitute your names for the generics I've used):

=DCount("[FieldName]","TableName","[FieldName] = True")

This next one will give you the False count:

=DCount("[FieldName]","TableName","[FieldName] = True")

Just another way...good luck as usual...
0
 
LVL 3

Expert Comment

by:tomk120999
ID: 2641452
Oops, sorry!  The second one should have "= False" not true (blush).  Look before you leap, right? :o)
good luck as usual...
0
 
LVL 30

Expert Comment

by:hnasr
ID: 2641479
In your detail section create two unbound fields:

Name          : y
Control Source: =IIf([fulltime]=-1,1,0)
Running sum   : Over All
Visible       : No

Name          : n
Control Source: =IIf([fulltime]=-1,0,1)
Running sum   : Over All
Visible       : No

In report footer, create two unbound fields:

Name          : y_count
Control Source: =[y]

Name          : n_count
Control Source: =[n]



0
 
LVL 10

Expert Comment

by:paasky
ID: 2641501
hi tomk,

I think DCount() would okay - even it takes more resources - when it's used counting grand totals, but you need to change criterias each time when you count different break level summaries. Something like

=DCount("[FieldName]","TableName","[FieldName] = True and [break1_fieldname='" & [break1_fieldname] & "'")

=DCount("[FieldName]","TableName","[FieldName] = True and [break2_fieldname='" & [break2_fieldname] & "'")

....

Other disadvange is how to restrict the count if the user runs the report with different criterias.

However you can copy/paste =SUM(...) fields into different break sections and they work without modification.

Best regards,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2641521
Another example how to calculate different counts:

=ABS(SUM([field1]="large"))
will count number of records in the report group having field1 value "large"

=ABS(SUM([field1]="medium"))
will count number of records in the report group having field1 value "medium"

=ABS(SUM([field1]="small"))
will count number of records in the report group having field1 value "small"

Paasky
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2641580
hnasr,
do you see any diffence between my and your comments?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:Believer
ID: 2641626
I *knew* hnasr's answer looked familiar!  (LOL)
0
 
LVL 3

Expert Comment

by:tomk120999
ID: 2641634
Hi, paasky, you make some good points, but if the domain for the DCount() is always the same as the record source for the report, then user criteria wouldn't matter as far as totals go.  I agree that you would have to set it up that way, but it doesn't sound too hard.  To get different breaks (grouping) would involve extended criteria as you say.  Thanks for the logic, the questioner always comes away with more than just an answer when we do this.  :)
good luck as usual...
0
 
LVL 10

Expert Comment

by:paasky
ID: 2641692
(Dedushka)
=IIf([fldBoolean]=True;1;0)
=IIf([fldBoolean]=False;1;0)

(hnasr)
=IIf([fulltime]=-1,1,0)
=IIf([fulltime]=-1,0,1)

there seems to be some minor differencies... but the principle is same

;-)

Paasky
 
0
 
LVL 30

Expert Comment

by:hnasr
ID: 2641772
Dedushka,

Actually not.  But this is the idea I used in commenting to another question.  

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10312634

Could not spot your comment in the crowd.
0
 
LVL 2

Expert Comment

by:WonHop
ID: 2647149
Hello.  This is a longer way of doing what everyone else has commented on.  I tested this in a form in the Open Event.
You should be able to do the same thing in the Report Open Event.

1. Make 2 queries.  1 with the criteria of Yes and the other with the criteria of No.

2.  Create 2 text boxes on your Report. 1 for Yes and 1 for No.

intCountYes  and  intCountNo

Then this code to your Report and to a Module

Report:
Private Sub Report_Open(Cancel As Integer)

Call CountYes
Me!intCountYes = CountYes

Call CountNo
Me!intCountNo = CountNo

End Sub

Module:
Function CountYes()

Dim Count_Yes As Recordset
Dim db As Database
 Set db = CurrentDb
 Set Count_Yes = db.OpenRecordset("QueryYes", dbOpenSnapshot)
 
 If Count_Yes.RecordCount < 1 Then
              CountYes = Count_Yes.RecordCount
 ElseIf Count_Yes.RecordCount > 0 Then
              Count_Yes.MoveLast
              CountYes = Count_Yes.RecordCount
End If


End Function



Function CountNo()

Dim Count_No As Recordset
Dim db As Database

 Set db = CurrentDb
 Set Count_No = db.OpenRecordset("QueryNo", dbOpenSnapshot)
 
 If Count_No.RecordCount < 1 Then
              CountNo = Count_No.RecordCount
 ElseIf Count_No.RecordCount > 0 Then
              Count_No.MoveLast
              CountNo = Count_No.RecordCount
End If

End Function

Hope this Helps

WonHop


0
 

Accepted Solution

by:
bird1427 earned 100 total points
ID: 2650528
On your query create two new fields:

yes:IIF([fieldname]="yes",1,0)
no:IIF([fieldname]="no",1,0)

The first field will return a 1 if the value in your field is yes and a 0 if it is not.The second field will return a 1 if the value in your field is no and a 0 if it is not. Once you have the two fields created in your query, you can use them in your report and create a text box with the formula =Sum([yes])in one and =Sum([no])in the other.

Granted, this is not as elegant or as developed as some of the other ways suggested. It is simple but it does work everytime.
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2651295
bird1427,
"a text box with the formula =Sum([yes])in one and =Sum([no])in the other" will not work, because fields [yes] and [no] are not bounded (by the way, it is not good practice to use filds with names that conflicts with keywords, such "yes", "no", etc.).
And try to post a comment instead of answer as everyone does here.
All the best,
Dedushka

0
 

Expert Comment

by:bird1427
ID: 2651602
You can include the new fields in your report but make them not visible. Then, you put an <unbound> text box in the report footer and create the suggested formula (=Sum([fieldname])) in the unbound text box. Unbound text boxes are used to display the results of a calculation, or to accept input from a user. Data in an unbound text box isn't stored anywhere. The new field names I proposed for the query were just examples. Accessed could call them anything he/she wants. However, it is better to make them as simple as possible so you remeber what they are for.
0
 
LVL 1

Expert Comment

by:Deverill
ID: 2655120
I'll have to agree with Dedushka about the field names.  Sure it may <work> but there is good coding style as well.  What happens 12 mths from now and you go back to it but we are using Access 2001: A Database Oddity?  What about the poor guy that has to maintain this when you are gone?  Wouldn't  SumOfYes  be a better name than   Yes  which may stop working some day and WILL confuse someone?  "Hmmm. A textbox named Yes.  Yes what!?" would be the muttering of your successor.  As you pointed out, it should be something "you remember what they are for".  

Another thing about reserved words is that they are !reserved!.  This means that MS could decide to do anything they want with them and not even feel guilty that your app breaks.

Sorry to butt in but I don't want anyone making more work for themselves later... that's what bosses are for. <grin>
0
 
LVL 1

Expert Comment

by:kodiakbear
ID: 6820213
Fixed the -1 problem on this question.
Escrow points corrected.

kb
Community Support Moderator
Experts Exchange
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

11 Experts available now in Live!

Get 1:1 Help Now