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

StacyC wrote:
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,
or a Rip-Rap voucher) and NOTHING ELSE. Other permitees can have those
things plus anything else on the list of 29 items....THEY are charged an
Overhead fee. The ones that have 3 or less of the ones in () are exempt from
paying an overhead fee. That is why i need to filter them out. {This policy
of changing who gets charged an OH fee just went into effect}. Does this
clarify ANYTHING?

On the basis of your HR analogy you seem to be saying you want to return
those permittees who hold 3 permits numbered 1 to 3, no more, no less, rather
[quoted text clipped - 21 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