Query Optimization Question

[SamirNaenenjad]

I have an application with an Access backend and I am trying to populate a list box with all the records from table A that aren't in table B. Right now, I am doing this with the following statement:

"SELECT Name, FROM A WHERE [Active] = 'Yes' AND Name NOT IN (SELECT Name FROM B WHERE SalesID = '001')"

This is workable when table B has a few records in it, but when I have up to 6,000 records in table B meeting the criteria, the query takes over a half hour to run (it is still running now).

Does anyone know if there is a way I can write a query like this that Access can handle better? Are there other alternatives I may be looking past? I have indexed the fields that matter, but it still isn't workable. Thanks in advance for any help.

-A.J.

 

[CosmoKramer]

You're missing a join. Check out the Find Unmatched Query Wizard.

 

[JerryDennison]

SELECT A.Name, A.Active, B.Name
FROM A LEFT JOIN B ON A.Name = B.Name
WHERE (((B.Name) Is Null AND (A.Active) = 'Yes'));

 

[SamirNaenenjad]

Thanks every one, this is working much better. Just curious, is this just the way Access likes it, or should I stay away from "NOT IN" queries with SQL Server, etc?

-A.J.

 

[JerryDennison]

Actually, Access is processing the query from the complete recordset. If you're using SQL Server, you'll probably want to create a stored procedure instead. What I gave you was a typical unmatched query structure. If you use the unmatched query wizard this is what it will create for you.