From: Stacy in Savannah Stacy in on
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
From: Bob Barrows on
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


From: StacyC on
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?
>

From: KARL DEWEY on
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
From: Bob Barrows on
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