Brain fart? What on earth...

[madEG]

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.

 

[DCrake]

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

David

 

[Rabbie]

Why not try something like

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

 

[madEG]

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.

 

[madEG]

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