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
|
|||
|
|||
Access Query
Hello... I have a database with data that looks like this:
ID_NUM STATUS 12345 O 12345 O 54321 C 54321 O 54321 O 67891 C 67891 C What I would like is a query that would look at each ID and return each ID, in this case, where all the STATUSes are 'C'. I don't want to return the ID that has a C and an O (54321) Thanks!! Submitted via EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks # 10 http://www.eggheadcafe.com/tutorials...-cool-net.aspx |
#2
|
|||
|
|||
Access Query
One method:
SELECT Id_NUM FROM SomeTable WHERE NOT Exists (SELECT * FROM SomeTable as Temp WHERE TEMP.Status "C" and Temp.Id_NUM = SomeTable.Id_NUM) To build this in query design view and not SQL view. == Add your table to a new query == Add ID_Num to the field list == Enter the following in a blank field "cell" replacing SomeTable with your table name: Exists (SELECT * FROM [SomeTable] as Temp WHERE TEMP.Status "C" and Temp.Id_NUM = [SomeTable].Id_NUM) == enter False in the criteria for this field John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Tim Arrington wrote: Hello... I have a database with data that looks like this: ID_NUM STATUS 12345 O 12345 O 54321 C 54321 O 54321 O 67891 C 67891 C What I would like is a query that would look at each ID and return each ID, in this case, where all the STATUSes are 'C'. I don't want to return the ID that has a C and an O (54321) Thanks!! Submitted via EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks # 10 http://www.eggheadcafe.com/tutorials...-cool-net.aspx |
#3
|
|||
|
|||
Access Query
well i suppose there is a couple ways of doing this. this is a quick one. I would turn on the "Totals" in the query, and set the Totals line for ID_NUM to "Group By" and the STATUS to "Max". Then set the criteria for STATUS to 'C'. MAX function will return the "largest" status, and since OC, will always return O if the ID_NUM has an O anywhere. With the criteria set to C, it will only return ID_NUM that has ONLY C's. hope that makes sense. |
#4
|
|||
|
|||
Access Query
Tim Arrington wrote:
Hello... I have a database with data that looks like this: ID_NUM STATUS 12345 O 12345 O 54321 C 54321 O 54321 O 67891 C 67891 C What I would like is a query that would look at each ID and return each ID, in this case, where all the STATUSes are 'C'. I don't want to return the ID that has a C and an O (54321) Yet another way, might(?) be faster than a subquery: SELECT ID FROM yourtable GROUP BY ID HAVING Count(*) = Sum(IIF(Status="C",1,0)) -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|