Counting in a Report

[Aurora]

I am using Access 2000

I created option groups with usually 1=yes and 2=no for
answers. In my Report I created calculations that said

if >ans is 1 give me yes and 2 give me no. That works OK.

Now I want to count the records with a yes answer in each
column. So I created the following formula for each
column;

=count([field]="1") But I am getting the wrong
answer. What am I doing wrong?????????

Aurora

 

[fredg]

I am using Access 2000

I created option groups with usually 1=yes and 2=no for
answers. In my Report I created calculations that said

if >ans is 1 give me yes and 2 give me no. That works OK.

Now I want to count the records with a yes answer in each
column. So I created the following formula for each
column;

=count([field]="1") But I am getting the wrong
answer. What am I doing wrong?????????

Aurora

Here is one method.
For Yes... (if Yes = 1)
=Sum(IIf([OptionField]=1,1,0))

For No... (if No = 2)
=Sum(IIf([OptionField]=2,1,0))


Note that the option value is a number datatype and therefor is NOT
enclosed within quotes.

If the only choices for the group is Yes or No, why are you using an
OptionGroup?
A simple Check Box would be in order here.
Counting check boxes then is a matter of just adding their values:
For Yes.. (value is -1)
=Abs(Sum([CheckBoxField]))

For No..(value is 0)
=Sum([CheckBoxField]+1)

 

[Marshall Barton]

I am using Access 2000

I created option groups with usually 1=yes and 2=no for
answers. In my Report I created calculations that said

if >ans is 1 give me yes and 2 give me no. That works OK.

Now I want to count the records with a yes answer in each
column. So I created the following formula for each
column;

=count([field]="1") But I am getting the wrong
answer.

You said the values in the field are numeric, but you're
comparing it to a string (because of the quotes). Getting
rid of the quotes isn't the entire answer because Count
counts the number of non-null values and neither true nor
false is null. Try this instead:

=Abs(Sum([field] = 1))

 

[Aurora]

The option group fields are text. I made them text so
that I could change the 1 to yes and 2 to no in the report.

Now I want to count the number of 1's (by each option
group field) to total in my report. Then I have to find
the average of yes's per field based on the total number
of records in the report.

Ex: sum by field 1 3 5 3
Avg by field 8% 25% 42% 25%

Aurora

-----Original Message-----

I am using Access 2000

I created option groups with usually 1=yes and 2=no for
answers. In my Report I created calculations that said

if >ans is 1 give me yes and 2 give me no. That works OK.

Now I want to count the records with a yes answer in each
column. So I created the following formula for each
column;

=count([field]="1") But I am getting the wrong
answer.

You said the values in the field are numeric, but you're
comparing it to a string (because of the quotes). Getting
rid of the quotes isn't the entire answer because Count
counts the number of non-null values and neither true nor
false is null. Try this instead:

=Abs(Sum([field] = 1))

 

[fredg]

The option group fields are text. I made them text so
that I could change the 1 to yes and 2 to no in the report.

Now I want to count the number of 1's (by each option
group field) to total in my report. Then I have to find
the average of yes's per field based on the total number
of records in the report.

Ex: sum by field 1 3 5 3
Avg by field 8% 25% 42% 25%

Aurora

-----Original Message-----

I am using Access 2000

I created option groups with usually 1=yes and 2=no for
answers. In my Report I created calculations that said
if >ans is 1 give me yes and 2 give me no. That works OK.

Now I want to count the records with a yes answer in each
column. So I created the following formula for each
column;

=count([field]="1") But I am getting the wrong
answer.

You said the values in the field are numeric, but you're
comparing it to a string (because of the quotes). Getting
rid of the quotes isn't the entire answer because Count
counts the number of non-null values and neither true nor
false is null. Try this instead:

=Abs(Sum([field] = 1))

Aurora,
Sorry, but an OptionGroup's value is NOT text, it is a number.
You may have manipulated the value using a function to show a text
"Yes" or "No", but the underlying value in the table is a Number.

In Form Design View, click on an OptionGroup button and try to change
it's value to anything but a number. You can't!!

Or... perhaps you are not actually using an OptionGroup, but just a
couple of text controls within a common rectangle.

In any event, both Marsh's code and mine will "count" for you, if you
are using an OptionGroup.