Access Query

[Tim Arrington]

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/tutorial...9247-d465960199fe/dr-dotnetskys-cool-net.aspx

 

[John Spencer]

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

 

[ghetto_banjo]

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 O>C, 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.

 

[Marshall Barton]

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))