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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|