How to Exclude result from one query from another

[QB]

I have 2 tables

1. Clients
2. Orders

In the orders we enter an order id (ie: 2100, 2101, 2102). For instance we
have

tbl_Orders
C_ID O_ID
1 1465
1 2100
1 2101
1 2201
2 2100
2 2101
3 1506
3 1709
4 2201

I need to build a list that list all the C_ID where the O_ID = 2100 OR 2101
but that the C_ID did not indlude O_ID < 2100.

For the example above:

2
4

How would I go about this?

QB

 

[QB]

Little mistake, It should only return 2 (not 4).

 

[John Spencer]

One of several methods

SELECT C_ID
FROM tbl_Orders
WHERE O_ID in (2100,2101)
AND NOT Exists (SELECT * FROM Tbl_orders as TEMP WHERE Temp.C_ID =
Tbl_Orders.C_ID and TEMP.O_ID <2100)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[QB]

It worked beautifully! Thank you so much. I had come up with some similar
code (not quite) and couldn't get it to work.

Perhaps you could illuminate me a little more.

In my original code, I was using a where statement like
WHERE ((O_ID =2100) AND (O_ID =2101))
and yet you use
WHERE O_ID in (2100,2101)

Why? I am very unfamiliar with the 'in' that you used. When is it better
to use this operator? Is it simply for long list or is it actually different
in some way?

Thank you,

QB

 

[John Spencer]

Well,
O_ID In(2100,2101)
is the same as writing
(O_ID = 2100 OR O_ID =2101)

Using the IN is just a convenient short hand way of writing the multiple or
statements.

Your statement was
WHERE ((O_ID =2100) AND (O_ID =2101))
which said that O_ID had to be simultaneously equal to 2100 and 2101 (an
impossibility).



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County