Incorrect recordset when querying large table (1 Viewer)

[mstorer]

I am running a set of queries to list open claims. The table used is much like a transaction ledger, listing payments, recoveries, as well as transaction indicators. This table is updated daily and has several years of data. Currently the table is only about 140,000 records. Due to the report requirements, I must run a negative subquery (Not in (Select...). This takes about 1hr 45m to run on a 700mhz Pentium III.

My problem is that the recordset is incorrect. If I narrow the data in the "Not In" table, I get the expected results. However, using the entire "Not In" table (about 8500 records) the query omits records it should not. Has anyone experienced this problem beore? Could it be a server issue or is a problem with Access? I have tested this several times, running the query on two different servers. However, I get the same result - the smaller the "Not In" table the more accurate the query recordset is. Thanks for any insight on this puzzling issue.

 

[Pat Hartman]

Another way to do the query which might be faster is to create a query that selects all the rows that would be in the "Not In" set. Then create a query that does a left join of the main table to the "not In" query and use criteria that looks for Null values in the key field of the "not In" query.

This might also solve the data problem.