Query criteria that would return results independent to the criteria in other fields

[mulamootil]

Hi - I tried to word the title as accurately as possible...i know I didn't do justice. I have a little situation here. I got a table (tbl_swab) with test results for swabs namely APC and Coliform (APC_Result and coliform_Result ) done on several equipments. I am wanting to count the number of times the test failed for both APC and Coliform separately.

Failure for APC is a test result greater than or equal to 100 and for coliform, test result greater than or equal to 10.

The problem that I am running into is that the below query first selects all the APC that meets the criteria and then looks for coliform that meets it's criteria.

Is there a way to view all the APC and coliform failures separately and then be able to count them in a report.

Here is the SQL statement.

SELECT Tbl_swab.Type, Tbl_swab.Sample_date, Tbl_swab.Equipment, Tbl_swab.APC_Result, Tbl_swab.Coliform_Result
FROM Tbl_swab
GROUP BY Tbl_swab.Type, Tbl_swab.Sample_date, Tbl_swab.Equipment, Tbl_swab.APC_Result, Tbl_swab.Coliform_Result
HAVING (((Tbl_swab.Type)="swab") AND ((Tbl_swab.APC_Result)>=100) AND ((Tbl_swab.Coliform_Result)>=10));

Please see the attached to have a look at the table


I appreciate any help. Thanks.

Stan

[mulamootil]

Sorry! the formatting of the sql statement got all messed up.

[NeoPa]

You could have a single query that included the tests as separate fields in your SELECT clause :

SELECT   [Type],
         [Sample_Date],
         [Equipment],
         [APC_Result],
         [Coliform_Result],
         IIf([APC_Result]>=100,1,0) AS [APC],
         IIf([Coliform_Result]>=10,1,0) AS [Coliform]

FROM     [Tbl_swab]

WHERE    (([Type]='swab')
  AND    (([APC_Result]>=100)
   OR    ([Coliform_Result]>=10)))

GROUP BY [Type],
         [Sample_Date],
         [Equipment],
         [APC_Result],
         [Coliform_Result]

I expect your report will be counting the values returned in [APC] and [Coliform].

By the way, I included the GROUP BY clause simply because you indicated the requirement by including it in yours. I doubt it should be there at all in truth. It does very little other than introduce the possibility of unforeseen errors. To remove it simply don't include it. No other changes would be required to the rest of it.

[mulamootil]

Hi NeoPa - It worked just perfect...learn something new everyday!! And you are right on the 'group by' comment. I took it away. Thanks again.

stan

[NeoPa]

A pleasure to help Stan :)