Prev: Look up tables vs. relationships design
Next: APPEND QUERY RUNS ON ITS OWN BUT NOT IN A MACRO
From: Jill on 8 Dec 2009 12:41
I have tables such as t_WeatherData in which I have fields such as
Temperature, WindSpeed, WindDirection. The WindSpeed and WindDirection
fields each have a lookup table with an ID (primary key/autonumber) and a
field that holds a category ("N", "NE", "NW", "W", etc., or "< 5 mph", "5-10
mph", etc.). As I have it, the look up tables merely serve to populate a
combo box on the data entry form...

OR, is creating a combo box with a value list sufficient?

What should be the factors in my decision on when to use a look up table vs.
value list in a combo box? I can think of one and that would be to use look
up tables for those situations where more values may be added... It seems to
me tables are just the all around solution because if you have to make any
changes, it's much easier in a table... ? But I am aware that caution must
be exercised because if a category is changed (made more refined/specific,
for example), then that will apply to all previously entered data because of
the ID... but then this brings me to my next question:

In the case of using lookup tables... What determines whether to use the ID
in the data table vs. just the category descriptor? To illustrate, I could
have my selection of "NE" in the combobox for WindDirection input the ID
associated with it into the field in t_WeatherData, or "NE" itself can go
into the field. What's best? Does it really matter?

Thank you, and these are my last questions for a while! I hope they were as
clear as mud. ;-)

Jill
From: David Benyo via AccessMonster.com on 8 Dec 2009 13:02
Jill, I almost always use the table method for many different reasons

- Adding values/choices
- Adding descriptor fields to the table
- Making a change updates all related tables (assuming the relationships are
set)
- it's called a relational database for a reason. Values are simply that...
you can't see how they relate to the other data without digging in. Thus, I
don't like using Value lists unless it's something that will NEVER change.

So that being said, setup a table of values. Use the combobox to store the ID
from this table, but don't display it. i.e. RowSource = "SELECT * FROM
tblOfValues", BoundColumn = 1 and ColumnWidths=0;1";1";1"..etc

If you are looking at the table that's related to your lookup table, setup
the field that uses these values to be the same as your combobox setup.
You'll be using the ID but never see it's there.

This is off the top of my head, but hopefully you get the idea.



Jill wrote:
>I have tables such as t_WeatherData in which I have fields such as
>Temperature, WindSpeed, WindDirection. The WindSpeed and WindDirection
>fields each have a lookup table with an ID (primary key/autonumber) and a
>field that holds a category ("N", "NE", "NW", "W", etc., or "< 5 mph", "5-10
>mph", etc.). As I have it, the look up tables merely serve to populate a
>combo box on the data entry form...
>
>OR, is creating a combo box with a value list sufficient?
>
>What should be the factors in my decision on when to use a look up table vs.
>value list in a combo box? I can think of one and that would be to use look
>up tables for those situations where more values may be added... It seems to
>me tables are just the all around solution because if you have to make any
>changes, it's much easier in a table... ? But I am aware that caution must
>be exercised because if a category is changed (made more refined/specific,
>for example), then that will apply to all previously entered data because of
>the ID... but then this brings me to my next question:
>
>In the case of using lookup tables... What determines whether to use the ID
>in the data table vs. just the category descriptor? To illustrate, I could
>have my selection of "NE" in the combobox for WindDirection input the ID
>associated with it into the field in t_WeatherData, or "NE" itself can go
>into the field. What's best? Does it really matter?
>
>Thank you, and these are my last questions for a while! I hope they were as
>clear as mud. ;-)
>
>Jill

--
Message posted via http://www.accessmonster.com

From: Mr. B on 8 Dec 2009 19:14
Jill,

I think you have pretty much already answered your own question. However,
just to provide some validation to your thoughts, I use the table method for
storing lists of options when I know that there may be a need to allow the
user to add items to the list, disable certain items in the list, have a
sorting capability with in the list that is not based on the list values but
on numeric value assigned by the user.

I use a hard coded list (Value list) when there will never be any change to
the list of values and I can enter them in the order I wan them to appear.

I also use one other method for providing various list to be used in combo
boxes and/or list boxes. I create a table that I normally call
"tblListInfo". This table has a field that identifies the ListType, the
ListValue, the ListSortValue and the RecordID.

I can then filter the recods from this table an have it provide values for
multiple small list from the same table.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm


"Jill" wrote:

> I have tables such as t_WeatherData in which I have fields such as
> Temperature, WindSpeed, WindDirection. The WindSpeed and WindDirection
> fields each have a lookup table with an ID (primary key/autonumber) and a
> field that holds a category ("N", "NE", "NW", "W", etc., or "< 5 mph", "5-10
> mph", etc.). As I have it, the look up tables merely serve to populate a
> combo box on the data entry form...
>
> OR, is creating a combo box with a value list sufficient?
>
> What should be the factors in my decision on when to use a look up table vs.
> value list in a combo box? I can think of one and that would be to use look
> up tables for those situations where more values may be added... It seems to
> me tables are just the all around solution because if you have to make any
> changes, it's much easier in a table... ? But I am aware that caution must
> be exercised because if a category is changed (made more refined/specific,
> for example), then that will apply to all previously entered data because of
> the ID... but then this brings me to my next question:
>
> In the case of using lookup tables... What determines whether to use the ID
> in the data table vs. just the category descriptor? To illustrate, I could
> have my selection of "NE" in the combobox for WindDirection input the ID
> associated with it into the field in t_WeatherData, or "NE" itself can go
> into the field. What's best? Does it really matter?
>
> Thank you, and these are my last questions for a while! I hope they were as
> clear as mud. ;-)
>
> Jill
 | 
Pages: 1
Prev: Look up tables vs. relationships design
Next: APPEND QUERY RUNS ON ITS OWN BUT NOT IN A MACRO