Counting "passes" and "fails"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arnold - 27 May 2008 04:41 GMT

OK - I've tried looking everywhere on the web and maybe am overlooking
something very simple
How can I sum up the number of "passes" for 6 fields that have either a
"pass", "fail" or is blank.
I have a report based on a query and it shows the results for multiple (6
fields) of tests for a number of projects.
I've tried dcount in the report footer, etc. but just can't find a solution
and I'm fairly new to Access with minimal VBA skills.

Any help appreciated!

Reply to this Message

John Spencer - 27 May 2008 13:13 GMT

I need to have a bit of clarification. Are you trying to sum up the results
for each field or are you trying to sum up the results for all 6 fields in one
control.

One field:
=Count(IIF(FieldA = "Pass",1,NULL))
Or an alternative that I prefer
=ABS(SUM(FieldA = "Pass"))

Blank probably equates to NULL, so you may need to use the following to get
results for that case.
= Abs(Sum(FieldA is Null))

Since you have 6 fields and three conditions for a total of 18 results, you
might want to consider using a sub-report based on a crosstab query. Although
with six fields you will have to use a union query to normalize the data in
the first place, and that may be a more complex solution than you want to tackle.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> OK - I've tried looking everywhere on the web and maybe am overlooking
> something very simple
[quoted text clipped - 6 lines]
>
> Any help appreciated!

Reply to this Message