You're not doing what he told you to do. Look again. You created two
mutually exclusive criteria and stated they both had to be true.
WHERE EXISTS (...) AND EXISTS (...)
It is impossible for both EXISTS to return True, hence zero records.

If you change the AND to OR so that one of them is allowed to be true,
the query would return ALL the records ... again, not what you want.
What Ken (and I in an earlier post) advised is to negate the second
criterion:

WHERE EXISTS (...) AND NOT EXISTS (...)


StacyC via AccessMonster.com wrote:
Ken,
Your solution is perfectly understandable (in logic) and see that is
the right direction; however, when i put this:
SELECT *
FROM tblPermitDetails
WHERE Exists
(SELECT *
FROM tblPermitDetails
WHERE ((tblPermitDetails.IDfacility) In (19,20,28))))
AND Exists
(SELECT *
FROM tblPermitDetails
WHERE ((tblPermitDetails.IDfacility) NOT In (19,20,28))));

into the SQL view, it provides me with 0 records. What am I missing
something? This is quite frustrating and seems to be unsolvable when
i know it IS solvable.

Stacy

KenSheridan wrote:
I'm still not really clear of the underlying criteria, I'm afraid.
If what you want are those permittees who hold any or all of the 3
relevant facilities, but do not hold permits for any facilities
other than these then a small amendment to the first subquery should
cater for that:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (1,2,3))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID 3);

This does of course assume that the PermitID values for the 3
relevant facilities are 1, 2 and 3 and all others are greater than
3. If the values for the 3 relevant ones do not in fact form a
subsequence at the start of the overall sequence you'd need to amend
the value list for the IN operator in the first subquery and use a
slightly different operation in the second. Say for instance the
relevant 3 values are 2, 4 and 8:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (2,4,8))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID NOT IN(2,4,8));

Ken Sheridan
Stafford, England

Almost...but somewhat reversed: I want to see the permitees who
have paid for in combination of 3 certain facilites (a walkway, an
underbrushing permit,
[quoted text clipped - 10 lines]
Permitee master table containing a field that identifies the
restricted Permitees?

--
HTH,
Bob Barrows