High level View of Multiple Lookup Tables

Posted on 2011-10-03
Last Modified: 2012-05-12

I am looking for views on a matter that must arise for all Access developers.

I create systems (smallish) that usually have a central "master" table that is linked to multiple lookup tables (via Combos)
For example; I might create a "Car" table and I would have combo lookups for
1. Car manufacturer
2. Country of Origin
3. Car Colour
4. etc.

Therefore , every time I create a new database I also create numerous lookup tables and numerous lookup forms. The structure of both the tables and the forms are almost identical in every case.

Is there a better way?

E.g. Perhaps I should create ONE table and ONE more clever lookup.  Or would this complicate matters?
 Any views - or am I just being lazy !
Question by:Patrick O'Dea
    LVL 61

    Accepted Solution

    While it may be possible to overdo it, I also generally use multiple lookup tables, with each lookup table having a distinct role.

    To avoid overdoing it, I consider what the restrictions on values may be.  If there is a reasonably limited list of distinct values for a given field, a lookup table is appropriate.

    You mentioned car color in your example... that is one I would be hesitant to create a seperate lookup table for.  The reason that jumps to mind (possibly not applicable in your case) is that depending on manufacturer, model, year, etc, there may be many ways of saying "Blue".  That is a field I'd allow free text entry for because of the wide variance of possibilities.  Another possibility in your forms for that if you do want to use a combo box in your main data entry form is to allow free entry in the combo box (Limit to List = No), but have the rowsource of the combo pulled from the main Car Information table.   That way, instead of using and maintaining a lookup table, the combo "learns" as new values are added to the main table.
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<E.g. Perhaps I should create ONE table and ONE more clever lookup.  Or would this complicate matters?
     Any views - or am I just being lazy ! >>

      No, your being efficent!  Correct answer is no; each type of lookup should have it's own lookup table, even if it only is a code and description.  Reason?  It's a fully normalized design.  You should not have to depend on the RDBMS to provide a consistent view of the data.

      If you have a single lookup table and you look at the lookup table directly, the range of values for each type are mixed.  For example, you should not be able to choose "BLUE" for a charge type which should Cash, Check, CC, etc.   To prevent that, you need to rely on a view and never directly go against the table.  Strictly speaking, that's a no-no.

       Do developers to it?  Yes<g>:

     screen shot lookup type table
     screen shot lookup value table

       This allows you to keep one maintenance form for the entire set of lookup tables.  I add in the "user modifiable", which keeps end users from modifying lookup lists that I have hooked into application logic.   However having the lookup values allows me to translate into other languages if need be.

       So as long as I have a single attribute of a description, I use this single table approach.  However if a lookup type has anything more then a single attribute of a description, then I break it out.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    You could also use a "template" database to do something like this. If you have a table which would commonly be used as a lookup, then export it to your Template database. When you build a new database, just import all the items from that Template database, which could include Tables, Forms, Modules, etc etc.

    Author Closing Comment

    by:Patrick O'Dea
    Thanks all for interesting, informative and diverse opinions!

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now