A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SQL subsets



 
 
Thread Tools Display Modes
  #11  
Old May 14th, 2010, 07:13 PM posted to microsoft.public.access.queries
StacyC[_2_]
external usenet poster
 
Posts: 5
Default SQL subsets

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?



  #12  
Old May 14th, 2010, 07:18 PM posted to microsoft.public.access.queries
StacyC[_2_]
external usenet poster
 
Posts: 5
Default SQL subsets

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?


  #13  
Old May 14th, 2010, 10:47 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default SQL subsets

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

  #14  
Old May 14th, 2010, 10:55 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default SQL subsets

On Fri, 14 May 2010 18:18:28 GMT, "StacyC" u60068@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]
  #15  
Old May 17th, 2010, 05:16 PM posted to microsoft.public.access.queries
StacyC via AccessMonster.com
external usenet poster
 
Posts: 1
Default SQL subsets

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

  #16  
Old May 17th, 2010, 06:39 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default SQL subsets

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.