-
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?
-
BTW, the query isn't simply taking a long time. The equivalent operation in FoxPro completed in about 2 minutes. Further, Access isn't responding to Ctrl+Break to halt the query.
-
Solution
Geez. Talking to myself here. This is the solution I used, as suggest by another friend. I created another query, qselOhioAddresses (street, city, state, expr1 [left(zipcode,5)]) to show all the address information, then I ran this query off the subquery.
SELECT street, city, state, expr1
FROM qselOhioAddresses LEFT JOIN tblZipLookup ON qselOhioAddresses.expr1 = tblZipLookup.zipcode
WHERE tblZipLookup.zipcode IS NULL;
Interesting approach, and maybe more roundabout than I would have liked, but it works fine.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|