Ok - Please someone point out what is my silly mistake...


SELECT *
FROM Customers
WHERE Customers.CustomerID not in (
select Trends.CustomerID
from Trends
);
I have a table called Trends and a table called Customers... The Trends table has a field called CustomerID, which is a fkey to the Customer table...

...so why when I ask to see all customers that are NOT in the trends table do I get nothing? Not an error, but no records returned...

It is quite clear that there are about a thousand or so Customers not linked to this table...

What am I doing wrong? Arg. :)

Does access not support NOT IN lists/subqueries?

Attached is an image that shows that there is Fkey's values in the Trends table, and clearly some customers w/o this linked value...

What gives? Do I need more coffee?

Thanks,
-Matt G.

What you need is an unmatched query. Try the wizard to step through the actions you need to perform.

David

Why not try something like
SELECT *
FROM Customers LEFT JOIN Trends ON Customers.CustomerID = Trends.CustomerID
WHERE Trends.CustomerID Is Null;

Ok, I never used the wizard before... it seems to have made a left join and where null... The trimmed down version of the resulting query was this:


SELECT *
FROM Customers LEFT JOIN Trends ON Customers.CustomerID = Trends.CustomerID
WHERE Trends.CustomerID Is Null;


But for the record: Does access not support what I wrote? I feel pretty good about it being valid sql. I am not slagging on access... Just curious...

Thanks,

-Matt G.

Rabbie, Thanks :)

I should have tried that, no? It does work. Thanks.

Still, just for nerd's sake - does what I wrote not work in Access? Or is it a jet db driver sql thingy?

Curious...