Slow querying combo box

[David Benyo via AccessMonster.com]

I have a combo box which is to display records not in another table.

tblEDLog has 180,000 records and will continue to grow
tblProcesses tracks processes on each record in tblEDLog

I'd like the combo box to display records from tblEDLog that aren't in
tblProcesses. The sql I have works, but is extremely slow because each record
is being checked. Doe anyone have any suggestions to speed up the query
result.

SELECT MedicalRecordNumber, AccountNumber FROM tblEDLog WHERE tblEDLog.
AccountNumber Not In (SELECT AccountNumber FROM tblProcesses);

Thanks for your ideas.

Dave

 

[KARL DEWEY]

See if this is faster --
SELECT tblEDLog.MedicalRecordNumber, tblEDLog.AccountNumber
FROM tblEDLog LEFT JOIN tblProcesses ON tblEDLog.AccountNumber =
tblProcesses.AccountNumber
WHERE (((tblProcesses.AccountNumber) Is Null));

 

[Klatuu]

This link will give you some good info on this situation:
http://allenbrowne.com/ser-32.html

 

[David Benyo via AccessMonster.com]

That works quite a bit faster. Thanks for the help.

See if this is faster --
SELECT tblEDLog.MedicalRecordNumber, tblEDLog.AccountNumber
FROM tblEDLog LEFT JOIN tblProcesses ON tblEDLog.AccountNumber =
tblProcesses.AccountNumber
WHERE (((tblProcesses.AccountNumber) Is Null));

I have a combo box which is to display records not in another table.

[quoted text clipped - 12 lines]

Dave

 

[David Benyo via AccessMonster.com]

Thanks for the link. I looked into this option, but wouldn't serve the
purpose as this is more for a lookup type combo. Granted my combo is a type
of lookup, but I need to display all records not processed yet.

I have computer illiterate user (actually jumped the first time seated in
front of a computer when the instructor told her to grab the mouse) that
needs to match records in the database with paper charts. Therefore she'll
need to see all records needing attention in-case she doesn't have the paper
chart in hand.

This link will give you some good info on this situation:
http://allenbrowne.com/ser-32.html

I have a combo box which is to display records not in another table.

[quoted text clipped - 12 lines]

Dave