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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.