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
|
|||
|
|||
Using exist/not exist in subquery
I have four containers in my containers table. I want to eliminate the three
that are in my transactions table and just retrieve one. Here is my sql code that won't return anything. SELECT DISTINCT tblContainer.ContainerID, tblContainer.ContainerName, tblContainer.BlockID FROM tblContainer WHERE (((tblContainer.BlockID)=[lstBlock].[Value])) and Not Exists (SELECT tblTransactions.TransTypeID, tblTransactions.ContainerID, tblTransactions.BlockID FROM tblTransactions WHERE ((tblTransactions.TransTypeID)=17)); There is a one to many relationship containers to transactions. Could that have anything to do with my problem? Any help would be greatly appreciated. -- Damon Heron |
#2
|
|||
|
|||
Using exist/not exist in subquery
You seem to be missing something in the subquery. It does not have any
restrictions other than transTypeID = 17. If there is one record in the entire table tblTransactions then Exists is true and Not Exists is false and therefore no records are returned. Perhaps you want to restrict the subquery to those records that match tblContainer.containerID and tblContainer.BlockID? SELECT DISTINCT tblContainer.ContainerID, tblContainer.ContainerName, tblContainer.BlockID FROM tblContainer WHERE tblContainer.BlockID=[lstBlock].[Value] and Not Exists ( SELECT * FROM tblTransactions as T WHERE T.TransTypeID=17 And T.ContainerID = tblContainer.ContainerID AND T.BlockID = tblContainer.BlockID) I fail to see how [lstBlock].[Value] works in this query. Is that a reference to a control on a form? If so, I would expect to see FORMS!FormName!lstBlock in the SQL vice what you have. Damon Heron wrote: I have four containers in my containers table. I want to eliminate the three that are in my transactions table and just retrieve one. Here is my sql code that won't return anything. SELECT DISTINCT tblContainer.ContainerID, tblContainer.ContainerName, tblContainer.BlockID FROM tblContainer WHERE (((tblContainer.BlockID)=[lstBlock].[Value])) and Not Exists (SELECT tblTransactions.TransTypeID, tblTransactions.ContainerID, tblTransactions.BlockID FROM tblTransactions WHERE ((tblTransactions.TransTypeID)=17)); There is a one to many relationship containers to transactions. Could that have anything to do with my problem? Any help would be greatly appreciated. -- Damon Heron |
#3
|
|||
|
|||
Using exist/not exist in subquery
Thank you John, you saved my Saturday!!!
-- Damon Heron |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return Multiple Values via Subquery | JDRaven | Running & Setting Up Queries | 3 | January 5th, 2006 08:38 PM |
Insert subquery when record does not exist... | [email protected] | General Discussion | 8 | October 24th, 2005 05:22 PM |
UNION in Subquery | jacob | Running & Setting Up Queries | 1 | May 18th, 2005 11:14 PM |
Updating master workbook from source that may/may not exist | [email protected] | Worksheet Functions | 20 | April 7th, 2005 03:37 PM |
problem printing report with subquery in recordsource | Jeff B | Setting Up & Running Reports | 2 | May 27th, 2004 02:35 PM |