Prev: query trouble
Next: Query using multiple values from one text box
From: StacyC on 14 May 2010 14:13
With 68 tables, 14 lookup tables: it was NOT the entire db structure. It was
2 tables and one view.
but thanks all the same.

Bob Barrows wrote:
>Sorry, but I really don't want to see your entire database structure :-)
>
>Boil it down to a concise summary that will not be too painful to type
>into a newsgroup post, because that's the only way I will look at it.
>Like this:
>
>tblPemitItems
>PermiteeID ItemID
>1 1
>1 2
>1 3
>2 1
>2 2
>2 3
>3 1
>4 2
>5 1
>5 4
>5 28
>6 5
>7 1
>7 2
>7 3
>7 4
>7 5
>8 1
>8 2
>8 3
>
>desired results:
>PermiteeID
>1
>2
>3
>4
>8
>
>A side benefit might be that it will allow you to see your solution
>yourself.
>
>> attached photos of relates and table views relevent ot this issue.
>> But I'n not sure how to tell ou to get there???
>[quoted text clipped - 9 lines]
>>>>>>> Permitee master table containing a field that identifies the
>>>>>>> restricted Permitees?
>

From: StacyC on 14 May 2010 14:18
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?



KenSheridan wrote:
>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
>than those who hold any 3 permits, but no more. If so try this:
>
>SELECT *
>FROM tblPermittees
>WHERE
> (SELECT COUNT(*)
> FROM tblPermitDetails
> WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
> AND PermitID IN (1,2,3)) = 3
>AND NOT EXISTS
> (SELECT *
> FROM tblPermitDetails
> WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
> AND PermitID >3);
>
>Ken Sheridan
>Stafford, England
>
>>Beginnng from the bottom:
>>No, there is no field that identifies restricted permittes
>[quoted text clipped - 19 lines]
>>>Permitee master table containing a field that identifies the restricted
>>>Permitees?

From: KenSheridan via AccessMonster.com on 14 May 2010 17:47
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/Forums.aspx/access-queries/201005/1

From: John W. Vinson on 14 May 2010 17:55
On Fri, 14 May 2010 18:18:28 GMT, "StacyC" <u60068(a)uwe> 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?
>
>
>
>KenSheridan wrote:
>>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
>>than those who hold any 3 permits, but no more. If so try this:
>>
>>SELECT *
>>FROM tblPermittees
>>WHERE
>> (SELECT COUNT(*)
>> FROM tblPermitDetails
>> WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
>> AND PermitID IN (1,2,3)) = 3
>>AND NOT EXISTS
>> (SELECT *
>> FROM tblPermitDetails
>> WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
>> AND PermitID >3);
>>

These queries should do exactly what you describe if you replace Ken's 1, 2, 3
with the permit IDs for these specific facilities. Just replace the

=3

with

<= 3

to pick up the cases where the person has only one or two permits.
--

John W. Vinson [MVP]
From: StacyC via AccessMonster.com on 17 May 2010 12:16
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/Forums.aspx/access-queries/201005/1

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