[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


High level View of Multiple Lookup Tables

Posted on 2011-10-03
Medium Priority
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

mbizup earned 668 total points
ID: 36902905
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 664 total points
ID: 36902974
<<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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 36903738
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
ID: 36911979
Thanks all for interesting, informative and diverse opinions!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 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