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
  #1  
Old May 14th, 2010, 02:10 PM posted to microsoft.public.access.queries
Stacy in Savannah
external usenet poster
 
Posts: 1
Default SQL subsets

I have a set of Permitees 12,000+, who own one or more items 58,908 of a list
of 29 items. Some Permitees are only allow to own 3 of these 29 items,
others can own 1-29. I need a SQL that will return DISTINCT permitees that
own 1-3 of those ONLY allowable items. Would anyone help guide me toward a
proper solution?

THANK YOU
  #2  
Old May 14th, 2010, 02:25 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default SQL subsets

Stacy in Savannah wrote:
I have a set of Permitees


:-) I initially read that as Pharisees ...

12,000+, who own one or more items 58,908
of a list of 29 items. Some Permitees are only allow to own 3 of
these 29 items, others can own 1-29. I need a SQL that will return
DISTINCT permitees that own 1-3 of those ONLY allowable items. Would
anyone help guide me toward a proper solution?

We'll need more information ... specifically, how to identify the
Permitees that are restricted to 3 of those 29 items. Oh! And how to
identify the restricted items ... Is there an item master table
containing a field that identifies the restricted items? Is there a
Permitee master table containing a field that identifies the restricted
Permitees?

--
HTH,
Bob Barrows


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

Beginnng from the bottom:
No, there is no field that identifies restricted permittes
Yes, there is a Master Table of of Items, but they are not segregated by
restriction type.
The Restricted items are simply a set of chosen numbers (the FK in
tblPermitDetails).

If you'll allow me...
Imagine with the standard HR database: You have employees that are
quailified to work in multiple departments ( Dept. 20, 30, 40, 60, 90) and i
want All Employees that are qualified to work in ONLY Dept's 30 and 90, NO
ONE else, even those that can work in 30, 90, and other dept. I just want
the ones that are owners of those 2 dept. training codes.

Does this help??
Thank you.

Bob Barrows wrote:

We'll need more information ... specifically, how to identify the
Permitees that are restricted to 3 of those 29 items. Oh! And how to
identify the restricted items ... Is there an item master table
containing a field that identifies the restricted items? Is there a
Permitee master table containing a field that identifies the restricted
Permitees?


  #4  
Old May 14th, 2010, 03:39 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default SQL subsets

Try this using your table and field names --
SELECT Permitee
FROM YourTable
GROUP BY YourTable.Permitee
HAVING (((Count(YourTable.[Items]))=3));


--
Build a little, test a little.


"Stacy in Savannah" wrote:

I have a set of Permitees 12,000+, who own one or more items 58,908 of a list
of 29 items. Some Permitees are only allow to own 3 of these 29 items,
others can own 1-29. I need a SQL that will return DISTINCT permitees that
own 1-3 of those ONLY allowable items. Would anyone help guide me toward a
proper solution?

THANK YOU

  #5  
Old May 14th, 2010, 03:55 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default SQL subsets

I'm sorry, but I'm not looking over your shoulder at your puter screen
:-)

All I know so far is you have a table called tblPermitItems. What are
the relevant fields in this table? Does this table maintain the link
between permitees and items? Hmm ... does the table have two fields?
PermiteeID and ItemID? And does it contain a list of Permitees and the
items they are allowed to use? If so, and you know the ItemIDs of
interest (say these IDs are 1,2 and 3 per your initial post), and let's
also assume you are interested only in those permitted to own all three
of those items (excluding those that have been granted use for one or
two of them) then:

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID = 3
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=1)
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=2)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

If you want all of the permitees restricted to any of the 1-3 items,
then

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID IN (1,2,3)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

StacyC wrote:
Beginnng from the bottom:
No, there is no field that identifies restricted permittes
Yes, there is a Master Table of of Items, but they are not segregated
by restriction type.
The Restricted items are simply a set of chosen numbers (the FK in
tblPermitDetails).

If you'll allow me...
Imagine with the standard HR database: You have employees that are
quailified to work in multiple departments ( Dept. 20, 30, 40, 60,
90) and i want All Employees that are qualified to work in ONLY
Dept's 30 and 90, NO ONE else, even those that can work in 30, 90,
and other dept. I just want the ones that are owners of those 2
dept. training codes.

Does this help??
Thank you.

Bob Barrows wrote:

We'll need more information ... specifically, how to identify the
Permitees that are restricted to 3 of those 29 items. Oh! And how to
identify the restricted items ... Is there an item master table
containing a field that identifies the restricted items? Is there a
Permitee master table containing a field that identifies the
restricted Permitees?


--
HTH,
Bob Barrows


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

OK, I tried that and did not get the results I needed. I'm probably not
articulating the goal/objective of my query precisely enough.
Thanks,
Stacy

Bob Barrows wrote:
I'm sorry, but I'm not looking over your shoulder at your puter screen
:-)

All I know so far is you have a table called tblPermitItems. What are
the relevant fields in this table? Does this table maintain the link
between permitees and items? Hmm ... does the table have two fields?
PermiteeID and ItemID? And does it contain a list of Permitees and the
items they are allowed to use? If so, and you know the ItemIDs of
interest (say these IDs are 1,2 and 3 per your initial post), and let's
also assume you are interested only in those permitted to own all three
of those items (excluding those that have been granted use for one or
two of them) then:

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID = 3
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=1)
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=2)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

If you want all of the permitees restricted to any of the 1-3 items,
then

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID IN (1,2,3)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

Beginnng from the bottom:
No, there is no field that identifies restricted permittes

[quoted text clipped - 20 lines]
Permitee master table containing a field that identifies the
restricted Permitees?



  #7  
Old May 14th, 2010, 05:54 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default SQL subsets

It never hurts to show us a few rows of sample data in tabular form, and
follow that with the results you wish to obtain, again in tabular form.

StacyC wrote:
OK, I tried that and did not get the results I needed. I'm probably
not articulating the goal/objective of my query precisely enough.
Thanks,
Stacy

Bob Barrows wrote:
I'm sorry, but I'm not looking over your shoulder at your puter
screen :-)

All I know so far is you have a table called tblPermitItems. What are
the relevant fields in this table? Does this table maintain the link
between permitees and items? Hmm ... does the table have two fields?
PermiteeID and ItemID? And does it contain a list of Permitees and
the items they are allowed to use? If so, and you know the ItemIDs of
interest (say these IDs are 1,2 and 3 per your initial post), and
let's also assume you are interested only in those permitted to own
all three of those items (excluding those that have been granted use
for one or two of them) then:

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID = 3
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID=1) AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems
WHERE ItemID=2) AND NOT PermiteeID IN (SELECT PermiteeID FROM
tblPermitItems WHERE ItemID NOT IN (1,2,3))

If you want all of the permitees restricted to any of the 1-3 items,
then

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID IN (1,2,3)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

Beginnng from the bottom:
No, there is no field that identifies restricted permittes

[quoted text clipped - 20 lines]
Permitee master table containing a field that identifies the
restricted Permitees?


--
HTH,
Bob Barrows



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

attached photos of relates and table views relevent ot this issue.
But I'n not sure how to tell ou to get there???
Stacy

Bob Barrows wrote:
It never hurts to show us a few rows of sample data in tabular form, and
follow that with the results you wish to obtain, again in tabular form.

OK, I tried that and did not get the results I needed. I'm probably
not articulating the goal/objective of my query precisely enough.

[quoted text clipped - 36 lines]
Permitee master table containing a field that identifies the
restricted Permitees?



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

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.

StacyC wrote:
attached photos of relates and table views relevent ot this issue.
But I'n not sure how to tell ou to get there???
Stacy

Bob Barrows wrote:
It never hurts to show us a few rows of sample data in tabular form,
and follow that with the results you wish to obtain, again in
tabular form.

OK, I tried that and did not get the results I needed. I'm probably
not articulating the goal/objective of my query precisely enough.

[quoted text clipped - 36 lines]
Permitee master table containing a field that identifies the
restricted Permitees?


--
HTH,
Bob Barrows


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

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

StacyC wrote:
Beginnng from the bottom:
No, there is no field that identifies restricted permittes
Yes, there is a Master Table of of Items, but they are not segregated by
restriction type.
The Restricted items are simply a set of chosen numbers (the FK in
tblPermitDetails).

If you'll allow me...
Imagine with the standard HR database: You have employees that are
quailified to work in multiple departments ( Dept. 20, 30, 40, 60, 90) and i
want All Employees that are qualified to work in ONLY Dept's 30 and 90, NO
ONE else, even those that can work in 30, 90, and other dept. I just want
the ones that are owners of those 2 dept. training codes.

Does this help??
Thank you.

We'll need more information ... specifically, how to identify the
Permitees that are restricted to 3 of those 29 items. Oh! And how to
identify the restricted items ... Is there an item master table
containing a field that identifies the restricted items? Is there a
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

 




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 05:43 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.