Return records only when all linked records meet criteria

Y

yator

I am trying to write a query that returns all records only when all [Result]
values are >12.0 ?

For example, the query below returns the following sample data:

SELECT tbl_trans.TransNo, tbl_lab.LabNo, tbl_lab.Result
FROM tbl_trans LEFT JOIN tbl_lab ON tbl_trans.Account = tbl_lab.Account
WHERE (((tbl_trans.LabMet)<>"Y") AND ((tbl_trans.PtType) Not Like "O"))
ORDER BY tbl_trans.TransNo

TransNo LabNo Result
3 4610 9.7
3 1098 9.6
3 1047 8.7
3 875 5.6
5 1303 12.1
5 1214 12.4
5 1094 12.5
8 2176 11.6
8 1415 11.9
8 871 9.6

How do I mofiy the query to return all [TranNo] 5 records only:

TransNo LabNo Result
5 1303 12.1
5 1214 12.4
5 1094 12.5

thanks!
 
J

John Spencer

SELECT tbl_trans.TransNo, tbl_lab.LabNo, tbl_lab.Result
FROM tbl_trans LEFT JOIN tbl_lab ON tbl_trans.Account = tbl_lab.Account
WHERE (((tbl_trans.LabMet)<>"Y") AND ((tbl_trans.PtType) Not Like "O"))
AND NOT Exists
(SELECT * FROM tbl_Trans
WHERE Temp.Result <=12 AND Temp.TransNo = Tbl_Trans.Transno
AND Temp.LabMet <>"Y" and Temp.PtType <> "O")
ORDER BY tbl_trans.TransNo

You might not need to include
AND Temp.LabMet <>"Y" and Temp.PtType <> "O"
in the exists query. Depends on your data.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jerry Whittle

SELECT tbl_trans.TransNo,
tbl_lab.LabNo,
tbl_lab.Result
FROM tbl_trans LEFT JOIN tbl_lab
ON tbl_trans.Account = tbl_lab.Account
WHERE tbl_trans.LabMet)<>"Y"
AND tbl_trans.PtType Not Like "O"
AND tbl_lab.Result > 12
ORDER BY tbl_trans.TransNo ;

However this assumes that the tbl_lab.Result field in a number and not text.
If it's text, you'll need to just through a few more hoops.
 
Y

yator

tbl_lab.Result is a numeric field.

I think my example did not show the whole range here. See new sample below.
Note that the query you suggested will return the records where [TransNo] =
5, but will also return the one record from [TransNo] = 3, [LabNo] = 1098,
where the [Result} >12.
I only want to return the 3 records for [TransNo]=5. ALL related [Result]
values for a specific [TransNo] must be >12.

TransNo LabNo Result
3 4610 9.7
3 1098 12.5
3 1047 8.7
3 875 5.6
5 1303 12.1
5 1214 12.4
5 1094 12.5
 
Y

yator

Is this query designe to creat a Temp table? I have not worked with those
before.

In any case, with this query Access asks for a parameter value for each of
the fields Temp.Result, Temp.TransNo, Temp.LabMet and Temp.PtType.

I am using Access 97 on XP....
 
J

John Spencer

My error. I left out the alias TEMP for tbl_Trans that is being used in the
sub-query. This does not create a temporary table, it just references
tbl_trans as a second instance of the existing table.

SELECT tbl_trans.TransNo, tbl_lab.LabNo, tbl_lab.Result
FROM tbl_trans LEFT JOIN tbl_lab ON tbl_trans.Account = tbl_lab.Account
WHERE (((tbl_trans.LabMet)<>"Y") AND ((tbl_trans.PtType) Not Like "O"))
AND NOT Exists
(SELECT * FROM tbl_Trans AS Temp
WHERE Temp.Result <=12 AND Temp.TransNo = Tbl_Trans.Transno
AND Temp.LabMet <>"Y" and Temp.PtType <> "O")
ORDER BY tbl_trans.TransNo

I apologize for the error and the confusion. I hope this will work for you.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Y

yator

Thanks John, that works.
The field {Resul} is fromthe linked table [tt_lab] so I had to add an alias
for that as well in the subquery:

SELECT tbl_trans.TransNo, tbl_lab.LabNo, tbl_lab.Result
FROM tbl_trans LEFT JOIN tbl_lab ON tbl_trans.Account = tbl_lab.Account
WHERE (((tbl_trans.LabMet)<>"Y") AND ((tbl_trans.PtType) Not Like "O"))
AND NOT Exists
(SELECT * FROM tbl_Trans AS Temp LEFT JOIN tbl_lab AS Temp2 ON
Temp.Account = Temp2.Account
WHERE Temp2.Result <=12 AND Temp.TransNo = Tbl_Trans.Transno)
ORDER BY tbl_trans.TransNo
 
Top