High level View of Multiple Lookup Tables


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 !
Patrick O'DeaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Patrick O'DeaAuthor Commented:
Thanks all for interesting, informative and diverse opinions!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.