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
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.
4 1844
Sorry! the formatting of the sql statement got all messed up.
NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
A pleasure to help Stan :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John young |
last post by:
I have been looking for an answer to a problem and have found this
group and hope you can assist .
I have been re doing a data base I have made for a car club I am with
and have been trying to...
|
by: Bill |
last post by:
Hello out there;
This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.
I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ......
|
by: Norma |
last post by:
I am trying to make a query pull data from between the dates I enter
in the parameter but also look back 'in time' to see where 2 other
fields have null values, and only pull data into the query if...
|
by: Martin Lacoste |
last post by:
Ok, before I headbutt the computer... don't know why when I add
criteria in a query, I get an 'invalid procedure call'. I also don't
know why after searching the help in access, the various access...
|
by: Andy |
last post by:
Hello,
I am having many problems with setting up a parameter query that
searches by the criteria entered or returns all records if nothing is
entered.
I have designed an unbound form with 3...
|
by: sofakingfree |
last post by:
Over the weekend I tested some new query logic in Access 2000 and it
worked flawlessly. I get into work this morning and try it in Access
2003 and it completely deletes the WHERE criteria. Why in...
|
by: Steve Jorgensen |
last post by:
Mailing List management is a good example of a case where my conundrum arises.
Say there is a m-m relationship between parties and groups - anyone can be a
member of any combintation of groups. ...
|
by: Macroman |
last post by:
MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard
drive
Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing
...
|
by: DeanL |
last post by:
Hi all,
I'm trying to set up a query that runs from a command button on a form
(simple enough so far), what I want the query to do is take values
from the fields on the form (seven fields in...
|
by: dizzydangler |
last post by:
Hi all,
I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |