Report - Need to count values with multi criteria

[TSI01]

I have a report that I am building. I have grouped it by company, month and job type and can count how many jobs a certain company did in a certain month.

There are 3 job types, a, b and c. Each has 2 tests.

Tests can result in pass, fail or noest.

What I need to do is count how many times for a certain company, did job a result come up as...

Job Test 1 Test 2

A Pass Pass
A Fail Pass
A Fail Fail
A Pass Fail
A NoEst NoEst

Etc.. can anyone help?

 

[Crowley16]

have you considered a text box with:

=sum(iif(testX = pass, 1, 0))

--------------------
Procrastinate Now!

 

[TSI01]

Tried, that, the problem is I need to count how many times that Test X returned a "pass" and Test X2 returned a "pass" for Job A.

Hope that makes sense... ?

 

[Crowley16]

well, just add the 2 sums together...

sum(iif(test1 = pass, 1, 0)) + sum(iif(test2 = pass, 1, 0))

and put this in the group fotter of your job...

--------------------
Procrastinate Now!

 

[TSI01]

Sorry that didn't work. Is it possible to use that same idea but use count if... test =" " and if ...test =" "?

ps. thanks for all your help. I also have empty fields, so ref to null values might help.

 

[traingamer]

I think we're getting overly complicated here. why not just:

 Select Job, [Test 1], [Test 2], Count(*) as cnt
from table
where ...
group by Job, [Test 1], [Test 2]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill