Using lookup tables Vs List Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 21 Jun 2006 11:55 GMT

I always thought that whenever a combobox was used in a form the rowsource
should come from a lookup table. E.g. tblUOM 1. Each, 2. KG, 3. Case. My
logic behind this was 1. It is centralised and can be easily updated and 2.
Storing an ID field (long number datatype) would use less space/resources
than storing a text value. I am seeing more and more examples of people
using listed values. I know lookup fields in tables are a no no but what
about list values within a combo on a form. Surely it would be better for
overheads to store values 1 or 2 relating to tblGender instead of values
male or female within a field. Whilst I'm on the subject, I've just tried
running the Project Management template in Access 2007 and rather than
having a tblProjectStatus with ProjectStatusID & ProjectStatus the table
uses list values which is apparently a "sin"!

Thanks

Reply to this Message

Rick Brandt - 21 Jun 2006 12:39 GMT

> I always thought that whenever a combobox was used in a form the
> rowsource should come from a lookup table. E.g. tblUOM 1. Each, 2.
[quoted text clipped - 10 lines]
> uses list values which is apparently a "sin"!
> Thanks

The number versus text issue is a non-issue because a value list can still do
multiple columns and therefore can still store numbers while displaying text.
The two main factors are whether the list will be static and how many places you
use the data.

As you state editing a table is far better then having to make a design change
to a form. Also if the same ComboBox list needs to appear on multiple forms
having them all pull from a common table source means making a change in one
place rather than in multiple places.

The sample databases provided by MS have seldom been sources for doing things
the best way. Always better to use them for ideas and then ask in these groups
for confirmaton.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Klatuu - 21 Jun 2006 14:19 GMT

So happy to read that I am not the only one who thinks the MS provided Access
templates don't use the best techniques and practices.

I concur with your post, Rick, but would like to thow in a little.
Old rules die hard. Once upon a time when we were working with 640K memory
and CPU speeds of 3.8K, minimizing everything was a necessity. Loading a
list of numbers took less memory and was faster than loading a list of text
items. And since we had very slow and very small discs (30MB was huge),
keeping the list in code was faster than retrieving it from disc. All
programmers learned how to stay within those bounds and still have programs
that executed as quickly as possible.

Now we have computers on our desk that are more powerful than the mainframes
of the 1970s. Considering efficiency is still a good idea, but now we can
consider other factors as well. For example, making it as easy as possible
for a user to understand our applications. So "Select 1, 2, 3, 4" is
efficient, it give no info. Select Red, Blue, Green, Yellow does. Sure,
takes a few more bytes and cycles, but the payoff to the user is worth it.

Lists for a Combo or List box are not necessarily a bad thing. The
questions to ask are:
What is the likelyhood this list of values will ever change?
How many places in the application will I need this list of values?

> > I always thought that whenever a combobox was used in a form the
> > rowsource should come from a lookup table. E.g. tblUOM 1. Each, 2.
[quoted text clipped - 24 lines]
> the best way. Always better to use them for ideas and then ask in these groups
> for confirmaton.

Reply to this Message

John Spencer - 21 Jun 2006 14:09 GMT

My personal choice is to use tables for choice lists.
-- Easily updatable; Only one place to add new choices for multiple forms
-- Users can update the source table (and you can control which users can do
so)
-- Easy to set display order
-- Easy to change display order if needed

In additiion, I tend to use a number field as the primary key, since users
often change their mind on the way they want something entered or displayed
in the choice list. For instance, Maryland versus MD versus MARYLAND for
state name. Not to mention that they will discover after they have entered
500 records that the actual spelling is Transportation instead of
Tranportation.

That said, there are cases where using list values may be easier and
quicker.

>I always thought that whenever a combobox was used in a form the rowsource
>should come from a lookup table. E.g. tblUOM 1. Each, 2. KG, 3. Case. My
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Ed Warren - 23 Jun 2006 11:48 GMT

I have found 'static lists' change and new requirements for forms emerge.
Therefore, I use tables along with lookup queries to load my form lists. I
have found little value in using the static list approach. Even something
as simple as 'telephoneType' undergoes growth.
At one time I coded "TelephoneNumber" in the base table.
Then I needed HomeTelephone, OfficeTelephone. (went to 1:M relationship)
Then I needed HomeTelephone, OfficeTelephone, OfficeFax
Then I needed HomeTelephone, OfficeTelephone,OfficeFax, HomeFax
Then I needed HomeTelephone,OfficeTelephone, OfficeFax, HomeFax, CellPhone,
In the future ?????

Note also above the mention of 'gender'.
The beginner would code Male:Female.
But should really use Male:Female:Not Disclosed

Then some requirements for medical records require Seven Genders.

Ed Warren.

> My personal choice is to use tables for choice lists.
> -- Easily updatable; Only one place to add new choices for multiple forms
[quoted text clipped - 27 lines]
>>
>> Thanks

Reply to this Message