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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1