Iif in group footer

  • Thread starter Thread starter Alice
  • Start date Start date
A

Alice

I have created a report grouped by company. It contains
names of trainees and 4 types of training results. I
want to count how many of each result each company has
achieved. I have used the iif function in the group
footer but it doesn't work e.g. iif([result]="pass",count
([result]),0). I've tried counting surname but that
doesn't work either. An ordinary count of all results
works fine.

If anyone can shed any light on this for me and point me
in the right direction they would be a great help.
 
If you will only ever have the same four types, you can use text boxes like:
=Sum( Abs([Result]="Pass") )

I would create a subreport based on a totals query that groups by company
and Results while counting Surname. Replace the 4 text boxes with the
subreport.
 
Thank you Duane. I've tried out the Abs function and it
works. However I would like to understand why and
don't. I know the Abs function in Excel returns the
absolute number without its sign, but this seems to be
weird given the data in the Access field is text! I
guess it assigns 1 to every field it finds that meets the
criteria - is that so?

If you have time can you indicate to me why the Iif
function doesn't work?

I'll give the sub-report a go another time as they are
completely new to me.

Thank you very much for your help.
-----Original Message-----
If you will only ever have the same four types, you can use text boxes like:
=Sum( Abs([Result]="Pass") )

I would create a subreport based on a totals query that groups by company
and Results while counting Surname. Replace the 4 text boxes with the
subreport.

--
Duane Hookom
MS Access MVP


I have created a report grouped by company. It contains
names of trainees and 4 types of training results. I
want to count how many of each result each company has
achieved. I have used the iif function in the group
footer but it doesn't work e.g. iif([result] ="pass",count
([result]),0). I've tried counting surname but that
doesn't work either. An ordinary count of all results
works fine.

If anyone can shed any light on this for me and point me
in the right direction they would be a great help.
Click to expand...


.
Click to expand...
 
The expression inside the Abs() evaluates to either true/-1 or false/0. This
means that every record where [Result]="Pass" will result in Abs(-1) which
is 1. Sum all the 1s and you have a count of records where the expression is
true.

Count() will count all records where the value is not null.

--
Duane Hookom
MS Access MVP
--

Alice said:
Thank you Duane. I've tried out the Abs function and it
works. However I would like to understand why and
don't. I know the Abs function in Excel returns the
absolute number without its sign, but this seems to be
weird given the data in the Access field is text! I
guess it assigns 1 to every field it finds that meets the
criteria - is that so?

If you have time can you indicate to me why the Iif
function doesn't work?

I'll give the sub-report a go another time as they are
completely new to me.

Thank you very much for your help.
-----Original Message-----
If you will only ever have the same four types, you can use text boxes like:
=Sum( Abs([Result]="Pass") )

I would create a subreport based on a totals query that groups by company
and Results while counting Surname. Replace the 4 text boxes with the
subreport.

--
Duane Hookom
MS Access MVP


I have created a report grouped by company. It contains
names of trainees and 4 types of training results. I
want to count how many of each result each company has
achieved. I have used the iif function in the group
footer but it doesn't work e.g. iif([result] ="pass",count
([result]),0). I've tried counting surname but that
doesn't work either. An ordinary count of all results
works fine.

If anyone can shed any light on this for me and point me
in the right direction they would be a great help.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top