Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Imports violate lookup criteria (2003)

    I have look up columns for many fields. Some of the look up columns are relatively large (200 choices), and all are limit to list. the problem is when adding new data, due to the volume, I do an import from excel - and the data flows in even if in violation of my limit to list fields... if I don't scour the raw data well, data comes in that violates the rules (misspellings, different abbreviations etc), and my subsequent queries are obviously flawed... After the data is in, I also try to go through periodically to spot inconsistent data, but as the file has gotten larger, that is not reasonable anymore (there are about 20,000 records now).. is there a mechanism that will pull out records that do not meet the criteria of the different look up fields?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Imports violate lookup criteria (2003)

    If you store the lookup values in separate tables and create relationships with enforced referential integrity between the main table and the lookup tables, you won't be able to import values that don't occur in the lookup tables. You might use this in the future to prevent new problems.
    As long as there are incorrect values, you won't be able to enforce referential integrity. You can find out which values cause problems by using the Find Unmatched Query Wizard - this will let you create a query that returns records from the main table that don't have a matching value in the lookup table for one of the lookup fields.

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Great, thanks!

    <P ID="nt"><font size=-1>(No Text)</font>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •