"In" subquery works, but "Not In" doesn't?

[Guest_imported]

In constructing a query to look for a few different records in two relatively large tables (test1 and Aeron-mem), I have run into a problem. The field I'm examining is "phonekey." Table "test1" has a few more records than "Aeron-mem", and I'd like to query it for these records. When using

In (SELECT phonekey FROM [Aeron-mem]

as the criterion for the query on the "phonekey" record of table "test1", the expected behavior occurs: all records are returned *except* the new records. However, when I add a "not" to the front of the criterion as so:

Not In (SELECT phonekey FROM [Aeron-mem]

I would expect the query to return all records from table "test1" with a "phonekey" NOT present in table [Aeron-mem]. This is not the case...I get a query with no records.

Can anyone help me?

Thanks! -Brian

 

[DougP]

use the RIGHT or LEFT join and see if that works

here is a DEMO I made up:

SELECT Customers.Custid, Customers.[Customer Name], CustomersOrders.Orderno, CustomersOrders.Orderdate
FROM CustomersOrders RIGHT JOIN Customers ON CustomersOrders.Custid = Customers.Custid;

I want all of the Customers, so its a RIGHT join. The customers are on the right side of the QBE grid in Access query designer. Also its what I want to see ALL records of.
DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.
Or visit my WEB site

http://www.barcodeone.com