Prev: query trouble
Next: Query using multiple values from one text box
From: Bob Barrows on 17 May 2010 13:39
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


First  |  Prev  | 
Pages: 1 2 3 4
Prev: query trouble
Next: Query using multiple values from one text box