If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Return records only when all linked records meet criteria
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! |
#2
|
|||
|
|||
Return records only when all linked records meet criteria
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 yator wrote: 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! |
#3
|
|||
|
|||
Return records only when all linked records meet criteria
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "yator" wrote: 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! |
#4
|
|||
|
|||
Return records only when all linked records meet criteria
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 |
#5
|
|||
|
|||
Return records only when all linked records meet criteria
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.... |
#6
|
|||
|
|||
Return records only when all linked records meet criteria
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 yator wrote: 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.... |
#7
|
|||
|
|||
Return records only when all linked records meet criteria
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 |
Thread Tools | |
Display Modes | |
|
|