Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Chicago, Illinois, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table-to-form references (Access 97&2000)

    Hello,
    I'm a new Access user and I have a question regarding referencing different columns on a table to different fields on a form. Specifically, my question is this: I have a table w/ 3 columns; an ID column (prim. key), an Error Code column, and an Error Description column. I'd like to know how to set criteria on a form so that when the Error Code is entered in one field, its respective Error description will automatically appear in a separate field. Also, on a related note, when I set up table columns to "lookup" to other tables, I know that the primary key and the column containing necessary values must both be brought over, but do I need to also bring over other columns? Any input is greatly appreciated. Thanks in advance for any responses.
    -PN-

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

    Re: Table-to-form references (Access 97&2000)

    Normally, you would enter only the error code, not the description as well. I'm assuming that you have a table somewhere that contains only the error codes and their related descriptions, so you could query on any two tables joined on the error code field in each in order to see the descriptions in reports, etc. As a general rule, you enter information (in this case, the error description) only once in a relational database. Then you use a key value (error code) to relate the records in that table to records in other tables and to retrieve the associated description.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Table-to-form references (Access 97&2000)

    To expand a bit on Charlotte's comments, the way you handle such situations on a form is to display the info in a combo box, which lets you see two or more fields from a related table based on the value in the main table. They also are used to simplify data entry - an inexperienced person might what to put the error code in based on the description, in which case you would only display the description, and the error code would be hidden. If on the other hand, the person entering data does it frequently, you might display the error code only, and display the description only when entering the code in order to allow the user to validate that the right code was typed. The other aspects of this kind of table design are that it saves space since you only store the description in one record rather than all of the recors in your main table, and it allows you to change the description by simply changing the one record in the related table (which could be a disadvantage in some situations).
    Wendell

Posting Permissions

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