Possible index corruption, or something else
I'm in the middle of migrating an important set of xBase (FoxPro) tables and programs to Access. I'm trying to write a query that checks a list of addresses (about 400,000 rows)against a list of valid zip codes (about 1,500 rows) and displays mismatches.
SELECT street, city, state, zipcode
FROM tblClaims
WHERE state='OH'
AND left(zipcode, 5) NOT IN (SELECT zipcode FROM tblZipLookup);
This query is supposed to show addresses with invalid Ohio zip codes so the user can correct them, but Access usually hangs and crashes. When it does run successfully, it gives incorrect results, falsely naming valid zip codes.
Because it only gives false positives with zip codes in the second half of my lookup table, I suspect the index for tblZipLookup.zipcode has become corrupted. I've tried running Compact and Repair Database, but no help. Is there something obviously wrong with the above query? Anyone know how to fix an index? Is 600M+ comparisons too much for Access to handle?