February 29, 2012 at 7:58 am
A friend asked me how to do this but do not know how.
It is actually in MS-Access but I know you guys know the concept.
I don't have actual data to post but I think it will be an easy question from you experts.
We need to query records where all 4 Test results are Negative.
What we are getting is if any one of the 4 Test results are Negative.
Here's the current query:
SELECT *
FROM Stays
INNER JOIN Orders ON Stays.AA_ID = Orders.PLAB_ACT
INNER JOIN Test_Results ON Orders.AA_ID = Test_Results.ACT_ATEST
WHERE Orders.[Order#] Between [oFrom] And [oTo]) AND
Test_Results.Test_Id In ("ULEU","UPRO","UBLD","UNIT") AND
Test_Results.Result="NEGATIVE"
We need only Orders where all four of the Test_Ids are Negative.
Thanks for any insight. Yeah, I know the SELECT * is not ideal, I just didn't know the columns he is planning to return.
February 29, 2012 at 8:08 am
Making the assumption that you are using the Test_Results table as a filter and don't actually need to see data from it, will something like the following work for you?
SELECT *
FROM Stays S
INNER JOIN Orders O
ON S.AA_ID = O.PLAB_ACT
WHERE EXISTS(
SELECT *
FROM Test_Results T
WHERE T.ACT_ATEST = O.AA_ID
AND T.Test_Id In ("ULEU","UPRO","UBLD","UNIT")
AND T.Result="NEGATIVE"
GROUP BY T.ACT_ATEST, TResult
HAVING COUNT(*) = 4
) AND Orders.[Order#] BETWEEN [oFrom] AND [oTo])
If you do actually need stuff from Test_Results, you can add it back in to the query.
February 29, 2012 at 10:13 am
It gives me an error with grouping on fields selected with '*'.
Here is what I have now:
SELECT DISTINCT Stays.[Billing#], Orders.[Order#]
FROM Stays INNER JOIN (Test_Results INNER JOIN Orders ON Test_Results.ACT_ATEST = Orders.AA_ID) ON Stays.AA_ID = Orders.PLAB_ACT
WHERE EXISTS(
SELECT * FROM Test_Results
WHERE Test_Results.ACT_ATEST = Orders.AA_ID
AND ((Test_Results.Test_Id) In ("ULEU","UPRO","UBLD","UNIT"))
AND ((Test_Results.Result)="NEGATIVE")
GROUP BY Test_Results.ACT_ATEST, Test_Results.Result
HAVING COUNT(*) = 4)
AND ((Orders.[Order#]) Between [oFrom] And [oTo])
AND ((Test_Results.[Group_test#]) Like "UA")
AND ((Orders.Ward_Ordering) Like "EMR");
I tried to select the Test_Results.ACT_ATEST instead of the * but it returned a bunch of duplicate rows.
Thanks again for your efforts. I think you have me on the right track.
February 29, 2012 at 12:51 pm
I am assuming there can only be one test ID and test result "Negative" combination per test ID
COUNT *
FROM (SELECT *
FROM Stays
INNER JOIN Orders ON Stays.AA_ID = Orders.PLAB_ACT
INNER JOIN Test_Results ON Orders.AA_ID = Test_Results.ACT_ATEST
WHERE Orders.[Order#] Between [oFrom] And [oTo]) AND
Test_Results.Test_Id In ("ULEU","UPRO","UBLD","UNIT") AND
Test_Results.Result="NEGATIVE")
HAVING COUNT(*) > 3
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply