Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look Up Tables - Best Method.....?? (Access 97 SR2)

    I find myself using lookup tables more and more, both for data integrity as well as efficiency, and have a general question.

    Is it better to have in my main table a lookupID field which will be linked to the autonumber field in the lookup table, or a text field which is populated as a result of looking up the lookup table if you know what i mean <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    The main difference is obviously that the field in the main table will contain only integers, which must be better from both a storage and sorting point of view.

    At present, i am of the opinion that it does not really matter, but i am interested in what other more experienced users would consider the "best" or "better" way of doing things.

    Thank you for your time.

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look Up Tables - Best Method.....?? (Access 97 SR2)

    With very few exceptions, I use autonumber keys in my lookup tables and insert that key into the target table. That provides maximum flexibility to handle things like misspellings or where someone decides the want a whole different phrase for a particular lookup value. Even a Year lookup table has an autonumber key that gets inserted into the year field because that way I can control the format (i.e., 4-digit year as opposed to 2-digit) without any extra effort.

    My users aren't allowed to see the tables themselves, so only the DBA knows that the values they see are not literally stored in each record.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Look Up Tables - Best Method.....?? (Access 97 SR2)

    >>I find myself using lookup tables more and more, both for data integrity as well as efficiency, and have a general question.

    Is it better to have in my main table a lookupID field which will be linked to the autonumber field in the lookup table, or a text field which is populated as a result of looking up the lookup table if you know what i mean.<<


    I'm assuming that by your 2nd alternative, your lookup table doesn't use an autonumber for the ID, just the text field (for example, a table of States would use the 2-letter Post Office abbreviation).

    To tell the truth, I use both. If the lookup table text info is relative short and the values aren't likely to change, then I won't use an autonumber for the ID, but will use a text field as Primary Key (as in State table). But if it is a long text field and/or likely to be changed or it is likely to contain either an apostrophe or a quotation mark, then I will use an autonumber as PrimaryKey.

    I figure I'm saving a little by not having to lookup the name each time if the ID field is text; that's just 1 less table I have to join in a query.

    But that's just the way I feel about it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look Up Tables - Best Method.....?? (Access 97 SR2)

    Thank you to both of you. Your time is, as always, appreciated <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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