By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

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

P: 33
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Tbl_swab.Type, Tbl_swab.Sample_date, Tbl_swab.Equipment, Tbl_swab.APC_Result, Tbl_swab.Coliform_Result
  2. FROM Tbl_swab
  3. GROUP BY Tbl_swab.Type, Tbl_swab.Sample_date, Tbl_swab.Equipment, Tbl_swab.APC_Result, Tbl_swab.Coliform_Result
  4. 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
Attached Files
File Type: pdf swab.pdf (5.5 KB, 235 views)
Mar 17 '10 #1

✓ answered by NeoPa

You could have a single query that included the tests as separate fields in your SELECT clause :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Type],
  2.          [Sample_Date],
  3.          [Equipment],
  4.          [APC_Result],
  5.          [Coliform_Result],
  6.          IIf([APC_Result]>=100,1,0) AS [APC],
  7.          IIf([Coliform_Result]>=10,1,0) AS [Coliform]
  8.  
  9. FROM     [Tbl_swab]
  10.  
  11. WHERE    (([Type]='swab')
  12.   AND    (([APC_Result]>=100)
  13.    OR    ([Coliform_Result]>=10)))
  14.  
  15. GROUP BY [Type],
  16.          [Sample_Date],
  17.          [Equipment],
  18.          [APC_Result],
  19.          [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.

Share this Question
Share on Google+
4 Replies


P: 33
Sorry! the formatting of the sql statement got all messed up.
Mar 17 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
You could have a single query that included the tests as separate fields in your SELECT clause :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Type],
  2.          [Sample_Date],
  3.          [Equipment],
  4.          [APC_Result],
  5.          [Coliform_Result],
  6.          IIf([APC_Result]>=100,1,0) AS [APC],
  7.          IIf([Coliform_Result]>=10,1,0) AS [Coliform]
  8.  
  9. FROM     [Tbl_swab]
  10.  
  11. WHERE    (([Type]='swab')
  12.   AND    (([APC_Result]>=100)
  13.    OR    ([Coliform_Result]>=10)))
  14.  
  15. GROUP BY [Type],
  16.          [Sample_Date],
  17.          [Equipment],
  18.          [APC_Result],
  19.          [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.
Mar 17 '10 #3

P: 33
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
Mar 18 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
A pleasure to help Stan :)
Mar 18 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.