Using exist/not exist in subquery

[Damon Heron]

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.

 

[John Spencer]

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]

Thank you John, you saved my Saturday!!!