Using MS Access/Query trouble
I am at a complete loss with this one.
Two tables (the current list of contacts and an update file) with one common field (membership ID).
The following queries deliver:
SELECT Count(*) FROM BaseTable; immediate, result 1109
SELECT Count(*) FROM BaseTable WHERE Lid IN (SELECT Lidnr FROM OtherTable); takes 20 seconds, result 361
SELECT * FROM BaseTable WHERE Lid IN (SELECT Lidnr FROM OtherTable); immediate, 361 records
SELECT * FROM BaseTable WHERE Lid NOT IN (SELECT Lidnr FROM OtherTable); 40 seconds, empty result set
SELECT * FROM BaseTable WHERE NOT lid IN (SELECT Lidnr FROM OtherTable); 40 seconds, empty result set
Why it takes longer to count than to display does not bother me too much, but where are the 748 records I am expecting from at least one of the last two queries?
Access 2010, 2002-2003 compatible .mdb
It takes longer to Count because Access has to do an aggregation, its not a matter of just looking at the result set's record count.
Is Lid unique? You are getting 1109 records, but it appears there may be only 361 unique entries and that may be why its not working.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA