Prev: query trouble
Next: Query using multiple values from one text box
From: StacyC on 14 May 2010 12:37
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?
>

From: Bob Barrows on 14 May 2010 12:54
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



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

From: Bob Barrows on 14 May 2010 13:59
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


From: KenSheridan via AccessMonster.com on 14 May 2010 14:08
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/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