J
John Hipskind
What are the alternatives to using Lookup fields, when I
want the user to select from a half dozen or so
predetermined values?
Thanks.
John
want the user to select from a half dozen or so
predetermined values?
Thanks.
John
H
HSalim
The proscription against lookup fields is limited to table design.
You can and should use lookups - using combo or List boxes in forms - just
dont use them at the table level.
HS
You can and should use lookups - using combo or List boxes in forms - just
dont use them at the table level.
HS
H
HSalim
To answer your request for elaboration:
This can be a chapter length discussion in a book.
I'll try to as clear as possible, and hope others will jump in to help as
well.
I'm purposely using your terminology to make it easier for you.
First, you should read "Evils of Lookup Fields in Tables" on Dev Ashish's
site
http://www.mvps.org/access/lookupfields.htm
Almost every data driven application is built upon such interwoven
relationships,
regardless of whether they are SQL based (using databases such as Access,
SQL Server DB2 and Oracle) or not ( using record handlers such as Btrieve).
There are two primary reasons for using lookup fields:
a. limit the selection to the list - which you have clearly understood, and
b. using a reference to a specific record in another table, rather than
storing
the entire selection itself.
A corollary benefit being that typographical errors can be eliminated.
A single column ancillary lookup table does not address b.
I don't know your exact situation, but say your ancillary table
was a list of recipes, which had one Text (50 chars) Column.
Say it and contained two entries:
Grandma's old fashioned Chocolate Chip cookies
GrandPa's Smelly-Fart Texas Firehouse Chili N Beans
rather than storing this long 50 char (= 50 bytes) string in your main
table, it would be
far more efficient to store a reference Number (8 bytes).
So, consider adding an ID Column to your ancillary tables, and Storing that
ID in your Main
Tables.
When we say table level, we mean that you should not define a column as
a lookup field in the datasheet view of the main table.
Although it makes your table look nice and easy to read, it causes too many
problems
down the road.
This feature has been available since at least Access 2.0 and perhaps since
the birth of Access.
Most experienced developers do not use it and couldn't care less if it
disappeared, yet it
has survived into all subsequent versions for some reason - perhaps to
ensure backward compatibility
or perhaps because it has remained popular with the public.
You can easily recreate the exact "view" of the table by creating a query
that joins
your main table with your Lookup table. You can use list and comboboxes in
forms
as you described.
In short, use them everywhere except in table design
In shorter, Just don't use them in table design.
(my pitiful attempt at humor
)
Regards
HS
--------------------------------
Thanks, but please elaborate.I guess I'm not sure what you mean by "table
level."
Here's my situation:
I created a series of ancillary "lookup" tables, each having a single field
of pre-entered values.
These so-called lookup tables are referenced within forms, using list boxes
from which to select values.
These values (along with other data) are then fed into that form's
corresponding table, my "main" tables.
So it seems as though the concepts of lookup fields and tables are tightly
interwoven.
(For example, if I look at the datasheet view of the "main" tables, I see
that the fields
that used list boxes to obtain their data are also presented as lists.)
Is this the proper methodology? Again, I'm unclear as to the concept of
"table level"
as it applies to lookup fields.
Thanks again.
John
--------------------------------
This can be a chapter length discussion in a book.
I'll try to as clear as possible, and hope others will jump in to help as
well.
I'm purposely using your terminology to make it easier for you.
First, you should read "Evils of Lookup Fields in Tables" on Dev Ashish's
site
http://www.mvps.org/access/lookupfields.htm
Almost every data driven application is built upon such interwoven
relationships,
regardless of whether they are SQL based (using databases such as Access,
SQL Server DB2 and Oracle) or not ( using record handlers such as Btrieve).
There are two primary reasons for using lookup fields:
a. limit the selection to the list - which you have clearly understood, and
b. using a reference to a specific record in another table, rather than
storing
the entire selection itself.
A corollary benefit being that typographical errors can be eliminated.
A single column ancillary lookup table does not address b.
I don't know your exact situation, but say your ancillary table
was a list of recipes, which had one Text (50 chars) Column.
Say it and contained two entries:
Grandma's old fashioned Chocolate Chip cookies
GrandPa's Smelly-Fart Texas Firehouse Chili N Beans
rather than storing this long 50 char (= 50 bytes) string in your main
table, it would be
far more efficient to store a reference Number (8 bytes).
So, consider adding an ID Column to your ancillary tables, and Storing that
ID in your Main
Tables.
When we say table level, we mean that you should not define a column as
a lookup field in the datasheet view of the main table.
Although it makes your table look nice and easy to read, it causes too many
problems
down the road.
This feature has been available since at least Access 2.0 and perhaps since
the birth of Access.
Most experienced developers do not use it and couldn't care less if it
disappeared, yet it
has survived into all subsequent versions for some reason - perhaps to
ensure backward compatibility
or perhaps because it has remained popular with the public.
You can easily recreate the exact "view" of the table by creating a query
that joins
your main table with your Lookup table. You can use list and comboboxes in
forms
as you described.
In short, use them everywhere except in table design
In shorter, Just don't use them in table design.
(my pitiful attempt at humor
)Regards
HS
--------------------------------
Thanks, but please elaborate.I guess I'm not sure what you mean by "table
level."
Here's my situation:
I created a series of ancillary "lookup" tables, each having a single field
of pre-entered values.
These so-called lookup tables are referenced within forms, using list boxes
from which to select values.
These values (along with other data) are then fed into that form's
corresponding table, my "main" tables.
So it seems as though the concepts of lookup fields and tables are tightly
interwoven.
(For example, if I look at the datasheet view of the "main" tables, I see
that the fields
that used list boxes to obtain their data are also presented as lists.)
Is this the proper methodology? Again, I'm unclear as to the concept of
"table level"
as it applies to lookup fields.
Thanks again.
John
--------------------------------