Query

[Sunil Somani]

Hi All,

i have one query in ms access Database 2002.
which is like:
select distinct ( Field1) from table1 where Field1 not in ( select Field1
from table2) ;

Table1 has more than 70000 records I guess even more..
So when i open ms access database and run this query it run fine and around
30-40 minutes it returns with result,
But after this If I try to run the same query again , no result at all no
matter how long I wait.
And again If I close the database reopen and run i get the results.

Could you please advise how to resolve such strange issue.

Please help.

Thank you,

Best regards,
Sunil Somani

 

[John W. Vinson]

Hi All,

i have one query in ms access Database 2002.
which is like:
select distinct ( Field1) from table1 where Field1 not in ( select Field1
from table2) ;

Table1 has more than 70000 records I guess even more..
So when i open ms access database and run this query it run fine and around
30-40 minutes it returns with result,
But after this If I try to run the same query again , no result at all no
matter how long I wait.
And again If I close the database reopen and run i get the results.

Could you please advise how to resolve such strange issue.

Try a "Frustrated Outer Join" query instead:

SELECT DISTINCT [Field1]
FROM table1 LEFT JOIN table2
ON table2.field1 = table1.field1
WHERE table2.field1 IS NULL;

There should be an Index on field1 in both tables - unique if appropriate.