SQL MINUS operator rejected by Jet?

I'm not familiar with this syntax. Does it mean to select all of the records from T1 where a matching "a=b" entry does not appear in T2. If so, you could do it this way:

SELECT a
FROM T1
WHERE a NOT IN
(SELECT b
FROM T2)

Hope this is along the lines of what you're looking for. Access SQL does not support the MINUS operator. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

Thank you Steve, indeed that's what this syntax means, but I tried to use the 'NOT IN' syntax before the minus but i found it extremely looooooong to execute, so i had to kill Access before it had finished executing the request.
I thought that the MINUS operator could solve that...

Thanks again

David

Yes, if you have large tables, this would not be very efficient. Appropriate indexing might help though. You might also be able to do it more efficiently using an appropriate JOIN between the two tables; for example:

SELECT T1.*
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.a = T2.b
WHERE T2.b Is Null;

Again, make sure that both fields referenced in the join are indexed. Let me know if this helps.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au