Counting specific records in a group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been trying to create a way for my report (based off a query with a calculation result of "PASS" or "FAIL") to count the records showing "PASS". In the report the [Results] field from the query is showing the correct results but when I try to create a count in the Employee footer with the "PASS" criteria it only returns the value of the last record of each group. I have tried an invisible text box [Passcounts] in the detail to assign a value of "1" to "PASS" and then count that field but it still only returns the last record value. It has to be something simple that I overlooked but I've run out of patience. I have also tried incrementing in VBA but I still get the same result. Any help would be greatly appreciated

Thanks

HeatherC
 
To "Count" a conditional expression in a Report or Group Header or Footer,
use an expression like:
= Sum( Abs( [Your Condition] ) )
For instance, if your field name is [Grade]
= Sum( Abs( [Grade] = "PASS" ) )
You can use any boolean expression (yes/no).

--
Duane Hookom
MS Access MVP


HeatherC said:
I have been trying to create a way for my report (based off a query with a
Click to expand...
calculation result of "PASS" or "FAIL") to count the records showing "PASS".
In the report the [Results] field from the query is showing the correct
results but when I try to create a count in the Employee footer with the
"PASS" criteria it only returns the value of the last record of each group.
I have tried an invisible text box [Passcounts] in the detail to assign a
value of "1" to "PASS" and then count that field but it still only returns
the last record value. It has to be something simple that I overlooked but
I've run out of patience. I have also tried incrementing in VBA but I still
get the same result. Any help would be greatly appreciated.
 
To "Count" a conditional expression in a Report or Group Header or Footer,
use an expression like:
= Sum( Abs( [Your Condition] ) )
For instance, if your field name is [Grade]
= Sum( Abs( [Grade] = "PASS" ) )
You can use any boolean expression (yes/no).
Click to expand...

Would it be a little faster to do this?

= Abs(Sum([Grade] = "PASS"))

Then the Abs function would only be done once rather than
once for each number summed. I don't know if it would make
any difference at all, just asking....

Tom Lake
 
I have seen this suggested before and it might work more efficiently but I
doubt it would add much. I use similar expressions to sum quantities where
specific conditions are true. For instance
=Sum( Abs([Gender] = "m") * [Score] )
This expression requires the Abs() function to be inside the sum since
[Score] or another numeric field might be either positive or negative. This
is the reason why I always place the Sum() on the "outside". It just keeps
my old brain working with a consistent method.

--
Duane Hookom
MS Access MVP


Tom Lake said:
To "Count" a conditional expression in a Report or Group Header or Footer,
use an expression like:
= Sum( Abs( [Your Condition] ) )
For instance, if your field name is [Grade]
= Sum( Abs( [Grade] = "PASS" ) )
You can use any boolean expression (yes/no).
Click to expand...

Would it be a little faster to do this?

= Abs(Sum([Grade] = "PASS"))

Then the Abs function would only be done once rather than
once for each number summed. I don't know if it would make
any difference at all, just asking....

Tom Lake
Click to expand...
 
Back
Top