 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008IN-Predicate means OR - I need AND
Hello, I have a table like this: tblEACodes: ============================== SK AQ_FKEY aqeEA_FKEY == ======= ==== 10 26 6 21 57 4 23 57 6 42 57 30 My SELECT clause: SELECT SK, AQ_FKEY FROM tblEACodes WHERE tblEACodes.aqeEA_FKEY In (4,6,30); shows all records if only one of the WHERE criteria is TRUE. What I need is a query showing me only SK if ALL criteria is met. So the IN-predicate is wrong for me. In this example the row with SK = 10 should not be displayed which happens with the IN-clause shown above. Any hints? Thank you, So, can the combination of AQ_FKEY and AgeEA_FKEY ever be repeated in your table. In other words, is the combination of those two fields always unique? If so, you could use SELECT SK, AQ_FKEY FROM tblEACodes WHERE AQ_FKEY in (SELECT AQ_FKEY FROM tblEACodes as B WHERE B.aqeEA_FKEY In (4,6,30) GROUP BY AQ_FKEY HAVING Count = 3) If the combination is not unique your query becomes a bit more complex in that you have to create a query that returns the unique combinations and then do the count on that SELECT SK, AQ_FKEY FROM tblEACodes WHERE AQ_FKEY in (SELECT B.AQ_FKEY FROM ( SELECT DISTINCT AQ_FKEY, aqeEA_FKEY FROM tblEACodes )as B WHERE B.aqeEA_FKEY In (4,6,30) GROUP BY B.AQ_FKEY HAVING Count = 3) John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Hello, > [quoted text clipped - 22 lines] > happens with the IN-clause shown above. > Any hints? Thank you, Hi John, I swear that I didn't read your post first. Great minds think alike! I like how you took it one more step to deal with duplicates.  SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > So, can the combination of AQ_FKEY and AgeEA_FKEY ever be repeated in your > table. In other words, is the combination of those two fields always unique? [quoted text clipped - 56 lines] > > happens with the IN-clause shown above. > > Any hints? Thank you, The SQL statement is doing exactly what you ask of it. You aren't asking anything about the AQ_FKEY field which I'm assuming is important in that there must be a 57 for each 4, 6, and 30 in the aqeEA_FKEY field. Something like this may work. If the combination of the AQ_FKEY and aqeEA_FKEY fields are not unique, it could return bogus records. SELECT * FROM TblEACodes Where TblEACodes.AQ_FKEY IN (SELECT TblEACodes.AQ_FKEY FROM TblEACodes WHERE TblEACodes.aqeEA_FKEY In (4,6,30) GROUP BY TblEACodes.AQ_FKEY HAVING Count(TblEACodes.aqeEA_FKEY)=3); SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Hello, > [quoted text clipped - 22 lines] > happens with the IN-clause shown above. > Any hints? Thank you, If you want the SK having all the specified aqeEA_FKEY, then put the required aqeEA_FKEY in a table (a temporary one? ) without duplicated values and use the technique exposed at http://www.mvps.org/access/queries/qry0016.htm It is essentially a simple inner join which acts like an intersection of two sets. Hoping it may help, Vanderghast, Access MVP > Hello, > [quoted text clipped - 22 lines] > happens with the IN-clause shown above. > Any hints? Thank you, Hello folks, two remarks: 1. combination of AQ_FKEY and aqeEA_FKEY ist UNIQUE 2. HAVING Count = 3 may be working, but: the number of rows may vary as you can see here: AQ_FKEY = 26 has one row AQ_FKEY = 57 has three rows I read the stuff (link from Michel) but I don't understand if it would work for me? As the number of rows and also the IN-clause's values are always different I have to support the number of criteria to fill the value in the HAVING-clause by frontend-code. Ok, I give John's first version a try SELECT SK, AQ_FKEY FROM tblEACodes WHERE AQ_FKEY in (SELECT AQ_FKEY FROM tblEACodes as B WHERE B.aqeEA_FKEY In (4,6,30) GROUP BY AQ_FKEY HAVING Count = 3) Thank you for your help. -- candide_sh > If you want the SK having all the specified aqeEA_FKEY, then put the > required aqeEA_FKEY in a table (a temporary one? ) without duplicated values > and use the technique exposed at > > http://www.mvps.org/access/queries/qry0016.htm Small error in my SQL statement. Last line should read HAVING Count(*) = 3 SELECT SK, AQ_FKEY FROM tblEACodes WHERE AQ_FKEY in (SELECT AQ_FKEY FROM tblEACodes as B WHERE B.aqeEA_FKEY In (4,6,30) GROUP BY AQ_FKEY HAVING Count(*) = 3) Hope this will work for you. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Hello folks, > [quoted text clipped - 32 lines] >> >> http://www.mvps.org/access/queries/qry0016.htm ------------------------ 2. HAVING Count = 3 may be working, but: the number of rows may vary as you can see here: ------------------------- You may have 26 rows for one AQ_FKEY, but the inner join will remove the rows NOT IN the list of wanted aqe FKey BEFORE making the count: indeed INTERSECTION between two sets removes elements (rows) NOT IN one of two sets. Vanderghast, Access MVP Michel, I don't understand your comment. Do you mean that the following query will give the wrong results? SELECT SK, AQ_FKEY FROM tblEACodes WHERE AQ_FKEY in (SELECT AQ_FKEY FROM tblEACodes as B WHERE B.aqeEA_FKEY In (4,6,30) GROUP BY AQ_FKEY HAVING Count(*) = 3) Or are you saying that if there were additional values for the combination of AQ_FKEY and aqeEA_FKEY for example the following. That then you would get back additional rows. tblEACodes: ============================== SK AQ_FKEY aqeEA_FKEY == ======= ==== 10 26 6 21 57 4 23 57 6 42 57 30 54 57 32 55 57 39 The query would then return 5 rows from the above set (all but SK = 10). If the poster wishes to limit the return to exactly those that have only the values 4,6, and 30 and no other associated aqeEA_FKEY associated values then the query does become a bit more complex. SELECT SK, AQ_FKEY FROM tblEACodes INNER JOIN (SELECT AQ_FKEY, Count(*) as CountAll FROM tblEACodes GROUP BY AQ_FKey) as Tmp ON TblEACodes.AQ_Fkey = Tmp.AQ_FKey WHERE AQ_FKEY in (SELECT AQ_FKEY FROM tblEACodes as B WHERE B.aqeEA_FKEY In (4,6,30) GROUP BY AQ_FKEY HAVING Count(*) = 3) AND Tmp.CountAll = 3 John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > ------------------------ > 2. HAVING Count = 3 may be working, but: [quoted text clipped - 7 lines] > > Vanderghast, Access MVP Hello John, the In (4,6,30) -part means these are the minimum criteria to be met. More rows that meet the criteria are no problem (like your example giving 5 rows for AQ_FKEY = 57 -- candide_sh Hello to all, at the end I used John's SQL-statement (message #7) and it works fine. Thank you for your help -- candide_sh Using the IN construction as you did, or using an INNER JOIN, if there is no dup, will produce the right result. My last comment was more about *how* the inner join was working by throwing away unwanted EA_Fkey. I thought the OP was having objections, or questions, in that particular matter. Vanderghast, Access MVP > Michel, > I don't understand your comment. Do you mean that the following query will [quoted text clipped - 60 lines] >> >> Vanderghast, Access MVP
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.