Results 1 to 6 of 6
  1. #1
    Tlo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    41

    Principle Behind Lookup Fields in Tables

    Hello World,



    I'm taking a class in Access where the instructor is teaching us how to create lookup fields in Access. However, I've posted an issue here before (that I've since resolved) and was told that lookup fields in tables are evil. I know enough of Access at this point to make something really dangerous, but am hesitant to move forward with my projects because I feel I may be missing a couple of important principles. So for example, I have a customer table, and want a field that shows if the customer is commercial or residential. The only way I know to do this, other than a lookup field (building a separate table with "Customer Types" and creating the relationship via lookup), would be to do the following:

    - create the second table called tblCustomerTypes
    - create a query that has both fields, Customer Name and Customer Type
    - create a form with combo boxes for each field
    - add a button to make the form add new records to the query

    So in actuality the query is storing the dynamic data, while the tables only hold descriptions of the data that will later be matched together. Is this a correct principle? Again I have created plenty of lookups in tables, and my database is functional at the moment, but I want to be sure I have the idea correct before going much further.

    Thanks in advance for the help!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Or simply have a CustType field in your table that requires a C or R and let your form validation control what is put in there. I see nothing wrong with the instructor teaching about lookup fields as long as he/she covers the pitfalls. http://access.mvps.org/access/lookupfields.htm

    I've only ever used them once; to control the user level option that can be set for the user (an actual custom database object) within the db. It was never intended to be queried or reported on.
    Make sure you're talking about lookup fields and not multi value fields.
    Last edited by Micron; 02-03-2016 at 09:58 PM. Reason: added link
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Tlo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    41
    Ok that makes sense for two options, but what about for say 25 options? Let's say we have an employee table with department as one of the fields, and there are 25 different departments to choose from. Thoughts?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ok that makes sense for two options, but what about for say 25 options? Let's say we have an employee table with department as one of the fields, and there are 25 different departments to choose from
    Doesn't matter how many options - 1 to 100+: I never use look up fields. Look up TABLES, yes..... all the time.
    But users never have access to a table and I don't use datasheet view. IMO, the reasons for NOT using look up fields are greater that the benefits of using look up fields.
    And if I need to display data in a datasheet view, I "roll my own". I have much greater control of how data is displayed/formatted.


    Same for MVF and layout view..... never use them.



    My $0.02........

  5. #5
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    to be clear - you can use lookups (i.e. with a combo) in a form, just not advisable in a table

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Tlo View Post
    Ok that makes sense for two options, but what about for say 25 options? Let's say we have an employee table with department as one of the fields, and there are 25 different departments to choose from. Thoughts?
    Then you need a related table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 30
    Last Post: 09-23-2015, 07:24 AM
  2. D lookup between two tables
    By punna111 in forum Access
    Replies: 5
    Last Post: 03-26-2015, 09:19 AM
  3. Lookup fields in tables
    By lefty2cox in forum Access
    Replies: 6
    Last Post: 05-23-2013, 01:28 PM
  4. Lookup Tables
    By corinthianw in forum Access
    Replies: 1
    Last Post: 02-17-2010, 01:31 PM
  5. Lookup Tables
    By JoeBio in forum Database Design
    Replies: 1
    Last Post: 09-01-2006, 07:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums