Using MS Access/Query trouble

Advertisement


Question
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

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.