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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|