Results 1 to 3 of 3

Thread: Possible index corruption, or something else

  1. #1
    Join Date
    Jul 2009
    Location
    Columbus, OH
    Posts
    3

    Question 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?

  2. #2
    Join Date
    Jul 2009
    Location
    Columbus, OH
    Posts
    3
    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.

  3. #3
    Join Date
    Jul 2009
    Location
    Columbus, OH
    Posts
    3

    Thumbs up 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
  •