Lookup Evils

I can only refer you to the link I provided to the Access MVP site. If the
difficulties with lookup fields do not matter to you, you are of course free
to use them.
I did mention earlier in the thread that Value Lists are suited to small
lists that are not subject to change.

"Sam" wrote in message
...
I agree with most of everything in this discussion, however.....I have
fields
in my master table that in the 'Data/Type' I used Lookup Wizzard and used
a
Table for my data to be looked up. This works perfectly. If the original
data changes, I make the change in my Lookup Table and everything is
updated.
Using a Value List for small amounts of data is OK, but if the data
contains
very many records the LookUp Table is much easier. I am using the process
in
Tables, Queries, Forms based on the query and Reports based on the query.

"BruceM" wrote:

Yes, Insert Lookup Field is the same as using the Lookup Wizard. The
point that the Access MVPs and others have been making is that using a
lookup FIELD in a table can lead to problems. For one thing, it is
difficult if not impossible to change a lookup field to a conventional
field.
Let's say you have a table (tblStatus) for creating status reports, with
information about sales, inventory, etc. If you have a table field for
Department, just make it a text field in the table. On the form, create
a
combo box that has Department as its record source. You have two options
now. You can select Value List as the Row Source Type, then in Row
Source
type in a list of departments separated by semi-colons. Or you can
create a
department table (tblDept), then create a query (qryDept) based on that
table. Using the query will let you specify a sort order. In combo box
properties, select Table/Query as the Row Source Type, then select
qryDept
as the Row Source. If tblDept has a PK and a department name, and
qryDept
has the same (in that order), you would set your column count to 2, the
column widths to 0";1", and the bound column to 1 if you want to view the
department name and store the PK. If you want to store the department
name
itself you would set the bound column to 2. You could also just leave PK
out of qryDept. When you create a combo box its row source and column
count
default to 1, and the bound column defaults to 1.
Note that I am describing the use of tools at the user interface level
(the
form) to facilitate data entry. This is the standard and time-tested
approach. People sometimes disagree about details, but not about the
concept. In this example, tblDept functions as a lookup table that
supplies
values for the Department field in tblStatus. There is no need for user
interface tools at the table level, since users should not be entering or
changing data except through forms. If you create your lookups at the
form
level (using combo boxes as I have described, or list boxes), lookup
fields
will never be an issue.
In case you have not seen this article, it provides a succinct summary of
the problems with lookup FIELDS in tables.
http://www.mvps.org/access/lookupfields.htm

"HelenJ" wrote in message
...
Thanks for your comprehensive answer Bruce. I think I will stick with
PKs
since as you say if you change a value then it will change everywhere -
which
is what I want in this case.

As for the lookups - I must confess that I am still a bit confused - if
you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use
more
than one column? Not something that I have ever done either!

I will carry on in happy designing mode :-)

Many thanks
"BruceM" wrote:

I don't think there is really a downside to adding a primary key (PK)
field.
If your table is a list of 50 states in the U.S.A., and if the state
name
is
all you are storing, then it probably doesn't matter if you have a PK,
but
it won't hurt either, and you may find you need it later. I'll just
add
here that my opinion is that if you are storing a single field it may
as
well be the actual value (e.g. the state name) as a PK in a related
table.
However, if you are storing the state name and an abbreviation (and
the
state flower or whatever), then you should probably store the PK, and
just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK.
We
have a situation where I work in which the person's name has to appear
as
it
was when a particular type of document was created, so I have to store
the
actual name. If I stored the PK, then when a person's name changes
the
old
records will show the new name, which is not acceptable. If on the
other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must
be
stored. For another example, consider payroll. A person whose name
changes
receives a W-2 form for the new name, even if some of the paychecks
were
made out to the former name. I'm not trying to win a point, but
rather
to
suggest you consider the effect on old data of a change to something
like
a
person's name (or a country name, for that matter), which is subject
to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List
as
the
Row Source Type (in the combo box properties), and by adding
Yes;No;Maybe
to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is
often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert
Lookup
Field. This is the thing that experienced developers urge you to
avoid.
The reasons have been discussed in numerous posts and articles. I
have
never used a lookup field, so have not experienced first-hand the
problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea
to
have
a primary key in the lookup table? Or could it just be a single
field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a
field
in
a
table (I assume this is what you mean by a lookup field) if you then
need
to
alter the values (yes OK you start to win your point here!) the
there
is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into
which
you
can put one of just a few words or phrases then you can bind a
combo
box
to
that field, and make the combo box row source a Value List. If you
have
a
longer list (states or departments or whatever), and especially if
you
have
a list that could one day change, then you may do better to make a
table
containing just those values, and to use that table as the combo
box
row
source. This is an entirely different matter than using a lookup
field
in a
table. Lookup tables are fine. There is a big difference between
a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables,
however
could
someone please explain how you avoid using them - is the answer
to
have
many
tiny tables with potentially only 2 fields (assuming it is
necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to
have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create
a
table for this.

Many thanks

Helen

Just passing along what I've learned here. Good luck with the project.

"HelenJ" wrote in message
...
Thanks Bruce - very comprehensive!

"BruceM" wrote:

Yes, Insert Lookup Field is the same as using the Lookup Wizard. The
point that the Access MVPs and others have been making is that using a
lookup FIELD in a table can lead to problems. For one thing, it is
difficult if not impossible to change a lookup field to a conventional
field.
Let's say you have a table (tblStatus) for creating status reports, with
information about sales, inventory, etc. If you have a table field for
Department, just make it a text field in the table. On the form, create
a
combo box that has Department as its record source. You have two options
now. You can select Value List as the Row Source Type, then in Row
Source
type in a list of departments separated by semi-colons. Or you can
create a
department table (tblDept), then create a query (qryDept) based on that
table. Using the query will let you specify a sort order. In combo box
properties, select Table/Query as the Row Source Type, then select
qryDept
as the Row Source. If tblDept has a PK and a department name, and
qryDept
has the same (in that order), you would set your column count to 2, the
column widths to 0";1", and the bound column to 1 if you want to view the
department name and store the PK. If you want to store the department
name
itself you would set the bound column to 2. You could also just leave PK
out of qryDept. When you create a combo box its row source and column
count
default to 1, and the bound column defaults to 1.
Note that I am describing the use of tools at the user interface level
(the
form) to facilitate data entry. This is the standard and time-tested
approach. People sometimes disagree about details, but not about the
concept. In this example, tblDept functions as a lookup table that
supplies
values for the Department field in tblStatus. There is no need for user
interface tools at the table level, since users should not be entering or
changing data except through forms. If you create your lookups at the
form
level (using combo boxes as I have described, or list boxes), lookup
fields
will never be an issue.
In case you have not seen this article, it provides a succinct summary of
the problems with lookup FIELDS in tables.
http://www.mvps.org/access/lookupfields.htm

"HelenJ" wrote in message
...
Thanks for your comprehensive answer Bruce. I think I will stick with
PKs
since as you say if you change a value then it will change everywhere -
which
is what I want in this case.

As for the lookups - I must confess that I am still a bit confused - if
you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use
more
than one column? Not something that I have ever done either!

I will carry on in happy designing mode :-)

Many thanks
"BruceM" wrote:

I don't think there is really a downside to adding a primary key (PK)
field.
If your table is a list of 50 states in the U.S.A., and if the state
name
is
all you are storing, then it probably doesn't matter if you have a PK,
but
it won't hurt either, and you may find you need it later. I'll just
add
here that my opinion is that if you are storing a single field it may
as
well be the actual value (e.g. the state name) as a PK in a related
table.
However, if you are storing the state name and an abbreviation (and
the
state flower or whatever), then you should probably store the PK, and
just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK.
We
have a situation where I work in which the person's name has to appear
as
it
was when a particular type of document was created, so I have to store
the
actual name. If I stored the PK, then when a person's name changes
the
old
records will show the new name, which is not acceptable. If on the
other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must
be
stored. For another example, consider payroll. A person whose name
changes
receives a W-2 form for the new name, even if some of the paychecks
were
made out to the former name. I'm not trying to win a point, but
rather
to
suggest you consider the effect on old data of a change to something
like
a
person's name (or a country name, for that matter), which is subject
to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List
as
the
Row Source Type (in the combo box properties), and by adding
Yes;No;Maybe
to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is
often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert
Lookup
Field. This is the thing that experienced developers urge you to
avoid.
The reasons have been discussed in numerous posts and articles. I
have
never used a lookup field, so have not experienced first-hand the
problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea
to
have
a primary key in the lookup table? Or could it just be a single
field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a
field
in
a
table (I assume this is what you mean by a lookup field) if you then
need
to
alter the values (yes OK you start to win your point here!) the
there
is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into
which
you
can put one of just a few words or phrases then you can bind a
combo
box
to
that field, and make the combo box row source a Value List. If you
have
a
longer list (states or departments or whatever), and especially if
you
have
a list that could one day change, then you may do better to make a
table
containing just those values, and to use that table as the combo
box
row
source. This is an entirely different matter than using a lookup
field
in a
table. Lookup tables are fine. There is a big difference between
a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables,
however
could
someone please explain how you avoid using them - is the answer
to
have
many
tiny tables with potentially only 2 fields (assuming it is
necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to
have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create
a
table for this.

Many thanks

Helen

On Thu, 23 Mar 2006 09:38:29 -0800, Sam
wrote:

I agree with most of everything in this discussion, however.....I have fields
in my master table that in the 'Data/Type' I used Lookup Wizzard and used a
Table for my data to be looked up. This works perfectly.

Ok, try sorting a Query by the lookup field.
Or try joining to another table joining by the lookup field.

Doesn't work!

Lookup fields are very good at getting new users from A to B to C.

They fall flat if the user wants to go past D.

John W. Vinson[MVP]

Everything the referenced link says is correct but there really are cases
where the Lookups won't cause any harm. Let me try to separate the times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key that is
different from the value you want to display. For example, you have a table
of department names and each department has an ID. Adding a lookup at the
table level will cause problems with both nested queries and VBA code due to
the confusion between the departmentID and the departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you want to
display. An example might be a State code table. This won't work if you
want to display the StateName. In that case the key field would be
different from the display field.
2. You have a small list of values and the value you display is the value
you want to save. An example might be Male, Female, Unknown. If the value
you want to save is a code - M, F, U then you should not use a table level
lookup. Keep in mind that combos and listboxes work fine with text values
but you need numeric values for option groups so if you want to use an
option group on a form, you'll have to work around the numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are not using
lookup value tables, you need to be very careful to keep multiple instances
of combos in sync. You will keep a validation rule at the table level to
ensure that bad data cannot be entered and use combos on forms to provide
pick lists but you will be responsible for making the same changes to the
table field's validation rule property and all combos based on that list.
The next version of Access will take care of this little problem of keeping
the value list and the combos in sync but the current and older versions do
not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is essentially
a table within a table. I have forms and reports and a table that I add to
all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

Thanks for pointing that out. I did a little more experimenting, and found
no problems in sorting by the lookup field when it uses a value list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" wrote in message
...
Everything the referenced link says is correct but there really are cases
where the Lookups won't cause any harm. Let me try to separate the times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key that is
different from the value you want to display. For example, you have a
table of department names and each department has an ID. Adding a lookup
at the table level will cause problems with both nested queries and VBA
code due to the confusion between the departmentID and the departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you want to
display. An example might be a State code table. This won't work if you
want to display the StateName. In that case the key field would be
different from the display field.
2. You have a small list of values and the value you display is the value
you want to save. An example might be Male, Female, Unknown. If the
value you want to save is a code - M, F, U then you should not use a table
level lookup. Keep in mind that combos and listboxes work fine with text
values but you need numeric values for option groups so if you want to use
an option group on a form, you'll have to work around the numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are not
using lookup value tables, you need to be very careful to keep multiple
instances of combos in sync. You will keep a validation rule at the table
level to ensure that bad data cannot be entered and use combos on forms to
provide pick lists but you will be responsible for making the same changes
to the table field's validation rule property and all combos based on that
list. The next version of Access will take care of this little problem of
keeping the value list and the combos in sync but the current and older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and a table
that I add to all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

My question is related to the whole discussion of "lookup evils".

I now understand the logic of only including the user interface (the lookup)
in the form rather than directly in the table. If you have many tables in
which there is a "Unit" field. The unit field stores a measurement unit (ft,
cm, m, L, gallon, etc.). As nearly every table in the database contains a
"Unit" field I wanted to create a lookup table in which to store the
appropriate units so that users are forced to enter the unit in a standard
form. I see that I can do this by using a combo box at the form level. But,
is it necessary or good design to create a relationship between the table
data is being entered into and the input table? I apologize if this question
isn't worded very well, I'm still learning the terminology.

Jessi

"BruceM" wrote:

Thanks for pointing that out. I did a little more experimenting, and found
no problems in sorting by the lookup field when it uses a value list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" wrote in message
...
Everything the referenced link says is correct but there really are cases
where the Lookups won't cause any harm. Let me try to separate the times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key that is
different from the value you want to display. For example, you have a
table of department names and each department has an ID. Adding a lookup
at the table level will cause problems with both nested queries and VBA
code due to the confusion between the departmentID and the departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you want to
display. An example might be a State code table. This won't work if you
want to display the StateName. In that case the key field would be
different from the display field.
2. You have a small list of values and the value you display is the value
you want to save. An example might be Male, Female, Unknown. If the
value you want to save is a code - M, F, U then you should not use a table
level lookup. Keep in mind that combos and listboxes work fine with text
values but you need numeric values for option groups so if you want to use
an option group on a form, you'll have to work around the numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are not
using lookup value tables, you need to be very careful to keep multiple
instances of combos in sync. You will keep a validation rule at the table
level to ensure that bad data cannot be entered and use combos on forms to
provide pick lists but you will be responsible for making the same changes
to the table field's validation rule property and all combos based on that
list. The next version of Access will take care of this little problem of
keeping the value list and the combos in sync but the current and older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and a table
that I add to all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

.. Putting the units in another table is an excellent idea.

It is indeed good design to create a relationship. However, if you add a
field to an existing table, you may need to run a series of Update Queries
to get you started before you join up the tables and 'Enforce Referential
Integrity'

You may even need to update some of the records by hand (using your combo
box in a form of course) before you can join your tables in the
Relationships Window. (I've even had to resort to calling one of the items
in my 'lookup' table 'Unknown' because the unit that the inputter used could
have been almost anything. But this just highlighted the necessity for the
table)

You can add a 'lookup' table more than once to the relationship window or in
a query grid so it can join to more than one table but I have a sinking
feeling that you may have a design problem when you say that you have many
tables in which there is a Unit field - but then maybe I'm being
unnecessarily gloomy.

Evi



"Jessi" wrote in message
...
My question is related to the whole discussion of "lookup evils".

I now understand the logic of only including the user interface (the
lookup)
in the form rather than directly in the table. If you have many tables in
which there is a "Unit" field. The unit field stores a measurement unit
(ft,
cm, m, L, gallon, etc.). As nearly every table in the database contains a
"Unit" field I wanted to create a lookup table in which to store the
appropriate units so that users are forced to enter the unit in a standard
form. I see that I can do this by using a combo box at the form level.
But,
is it necessary or good design to create a relationship between the table
data is being entered into and the input table? I apologize if this
question
isn't worded very well, I'm still learning the terminology.

Jessi

"BruceM" wrote:

Thanks for pointing that out. I did a little more experimenting, and
found
no problems in sorting by the lookup field when it uses a value list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep
doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" wrote in message
...
Everything the referenced link says is correct but there really are
cases
where the Lookups won't cause any harm. Let me try to separate the
times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key that is
different from the value you want to display. For example, you have a
table of department names and each department has an ID. Adding a
lookup
at the table level will cause problems with both nested queries and
VBA
code due to the confusion between the departmentID and the
departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you
want to
display. An example might be a State code table. This won't work if
you
want to display the StateName. In that case the key field would be
different from the display field.
2. You have a small list of values and the value you display is the
value
you want to save. An example might be Male, Female, Unknown. If the
value you want to save is a code - M, F, U then you should not use a
table
level lookup. Keep in mind that combos and listboxes work fine with
text
values but you need numeric values for option groups so if you want to
use
an option group on a form, you'll have to work around the numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are not
using lookup value tables, you need to be very careful to keep
multiple
instances of combos in sync. You will keep a validation rule at the
table
level to ensure that bad data cannot be entered and use combos on
forms to
provide pick lists but you will be responsible for making the same
changes
to the table field's validation rule property and all combos based on
that
list. The next version of Access will take care of this little problem
of
keeping the value list and the combos in sync but the current and
older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and a
table
that I add to all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to
have
many
tiny tables with potentially only 2 fields (assuming it is necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create a
table for this.

Many thanks

Helen

Thank you for the reply Evi.

I thought I would provide a more concrete example.

tblWellCasingDetails
StationID (pk)
ComponentID (pk)
ComponentDescription
InnerDiameter
InnerDiameterUnit
TopZoneDepth
BottomZoneDepth
DepthUnit
DepthReference
Comments

tblHorizontalSurveyData
StationID (pk)
SurveyDate
SurveyGeneration (pk)
CurrentSurvey
SurveyCompany
XCoordinate
YCoordinate
CoordinateUnit
CoordinateSystem
HorizontalDatum
Notes

tblMeasurementUnits
Unit (pk)
UnitFullName
MeasurementSystem
MeasurementType

The first two tables above both contain fields which require an input for
the "Unit". In fact, tbl:WellCasingDetails has two fields which require an
input for the "Unit" (InnerDiameterUnit and DepthUnit). The table
tbl:MeasurementUnits is meant to serve as a look up table for the two tables
above and several others. I can create a relationship between
tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential
integrity. However, I run into a problem when I try to create a relationship
from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to the
tbl:MeasurementUnits. I can create the relationships, but cannot enforce
referential integrity. Any suggestions?

Jessi



"Evi" wrote:

.. Putting the units in another table is an excellent idea.

It is indeed good design to create a relationship. However, if you add a
field to an existing table, you may need to run a series of Update Queries
to get you started before you join up the tables and 'Enforce Referential
Integrity'

You may even need to update some of the records by hand (using your combo
box in a form of course) before you can join your tables in the
Relationships Window. (I've even had to resort to calling one of the items
in my 'lookup' table 'Unknown' because the unit that the inputter used could
have been almost anything. But this just highlighted the necessity for the
table)

You can add a 'lookup' table more than once to the relationship window or in
a query grid so it can join to more than one table but I have a sinking
feeling that you may have a design problem when you say that you have many
tables in which there is a Unit field - but then maybe I'm being
unnecessarily gloomy.

Evi



"Jessi" wrote in message
...
My question is related to the whole discussion of "lookup evils".

I now understand the logic of only including the user interface (the
lookup)
in the form rather than directly in the table. If you have many tables in
which there is a "Unit" field. The unit field stores a measurement unit
(ft,
cm, m, L, gallon, etc.). As nearly every table in the database contains a
"Unit" field I wanted to create a lookup table in which to store the
appropriate units so that users are forced to enter the unit in a standard
form. I see that I can do this by using a combo box at the form level.
But,
is it necessary or good design to create a relationship between the table
data is being entered into and the input table? I apologize if this
question
isn't worded very well, I'm still learning the terminology.

Jessi

"BruceM" wrote:

Thanks for pointing that out. I did a little more experimenting, and
found
no problems in sorting by the lookup field when it uses a value list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep
doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" wrote in message
...
Everything the referenced link says is correct but there really are
cases
where the Lookups won't cause any harm. Let me try to separate the
times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key that is
different from the value you want to display. For example, you have a
table of department names and each department has an ID. Adding a
lookup
at the table level will cause problems with both nested queries and
VBA
code due to the confusion between the departmentID and the
departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you
want to
display. An example might be a State code table. This won't work if
you
want to display the StateName. In that case the key field would be
different from the display field.
2. You have a small list of values and the value you display is the
value
you want to save. An example might be Male, Female, Unknown. If the
value you want to save is a code - M, F, U then you should not use a
table
level lookup. Keep in mind that combos and listboxes work fine with
text
values but you need numeric values for option groups so if you want to
use
an option group on a form, you'll have to work around the numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are not
using lookup value tables, you need to be very careful to keep
multiple
instances of combos in sync. You will keep a validation rule at the
table
level to ensure that bad data cannot be entered and use combos on
forms to
provide pick lists but you will be responsible for making the same
changes
to the table field's validation rule property and all combos based on
that
list. The next version of Access will take care of this little problem
of
keeping the value list and the combos in sync but the current and
older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and a
table
that I add to all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to
have
many
tiny tables with potentially only 2 fields (assuming it is necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create a
table for this.

Many thanks

Helen

I can't comment on your table structure - I've no experience in this kind of
db but I can see why you wanted all those unit fields now.

Does it tell you why you can't re-inforce ref integ?
If it's because you want to link the table twice, then just add the units
table a second time to the relationship window.

I suspect that it is because you have made the Unit Abbreviation the Primary
Key field of the Units Table but someone has not used your current
convention (eg they may have put a space in front of the l or written cms
instead of cm).
We can spot the faulty entries by using a Find Unmatched Query to find
anything in your main table's units field that isn't in your Units table.
You can then edit those entries by hand

Switch on Name Autocorrect, just for now. (switch it off when you've
finished everything)
I'm not sure that it is a good idea to use the Unit Abbreviation field as
the Primary Key field (someone else can confirm or deny this). Autonumbers
are safer because they can't be edited at all. They look ugly in tables but
the user need never see them. The combo boxes will contain them in their
first column but the wizard will encourage you to hide this so that the user
will only see the second column with the actual letters.
Lets say you add an Autonumber field to your Units table (call it UnitID)

To start with, add a number field to your Main Input table, never mind what
you call it, lets say also UnitID. In the end you can rename it by the same
name as your original field if you have to.

Once you have used your FindUnmatched table to check that both table have
the same data in the MeasurementUnit field then you can put both tables into
a query window and join them by the Measurement unit field (ie not the ID
field)

Check that the Input table still has the same number of records as before
you joined them. Put the letter fields from both tables into your query grid
and check if they have the same letters in them. (a fairly quick glance at
an example of each unit should do the trick)
Remove the other fields. Change the query to an Update Query. Add the UnitId
field from your Main table.
In the Update To line put

tblMeasurementUnits!UnitID

Do the same for the other UnitID type fields in your other tables.

Because the tables are linked, the UnitID field in your main table will now
have the correct ID number so that if it is added to a query with the
UnitAbbreviation field from the Measurements table you will see the correct
measurement.
Change the query back to a Select query and add fields and in the query
window to and check that all the fields in your main table are now filled.
then you can decide if you want to rename your Main Table's UnitID Field to
the name of your letter field.

Evi



"Jessi" wrote in message
...
Thank you for the reply Evi.

I thought I would provide a more concrete example.

tblWellCasingDetails
StationID (pk)
ComponentID (pk)
ComponentDescription
InnerDiameter
InnerDiameterUnit
TopZoneDepth
BottomZoneDepth
DepthUnit
DepthReference
Comments

tblHorizontalSurveyData
StationID (pk)
SurveyDate
SurveyGeneration (pk)
CurrentSurvey
SurveyCompany
XCoordinate
YCoordinate
CoordinateUnit
CoordinateSystem
HorizontalDatum
Notes

tblMeasurementUnits
Unit (pk)
UnitFullName
MeasurementSystem
MeasurementType

The first two tables above both contain fields which require an input for
the "Unit". In fact, tbl:WellCasingDetails has two fields which require
an
input for the "Unit" (InnerDiameterUnit and DepthUnit). The table
tbl:MeasurementUnits is meant to serve as a look up table for the two
tables
above and several others. I can create a relationship between
tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential
integrity. However, I run into a problem when I try to create a
relationship
from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to
the
tbl:MeasurementUnits. I can create the relationships, but cannot enforce
referential integrity. Any suggestions?

Jessi



"Evi" wrote:

.. Putting the units in another table is an excellent idea.

It is indeed good design to create a relationship. However, if you add a
field to an existing table, you may need to run a series of Update
Queries
to get you started before you join up the tables and 'Enforce
Referential
Integrity'

You may even need to update some of the records by hand (using your
combo
box in a form of course) before you can join your tables in the
Relationships Window. (I've even had to resort to calling one of the
items
in my 'lookup' table 'Unknown' because the unit that the inputter used
could
have been almost anything. But this just highlighted the necessity for
the
table)

You can add a 'lookup' table more than once to the relationship window
or in
a query grid so it can join to more than one table but I have a sinking
feeling that you may have a design problem when you say that you have
many
tables in which there is a Unit field - but then maybe I'm being
unnecessarily gloomy.

Evi



"Jessi" wrote in message
...
My question is related to the whole discussion of "lookup evils".

I now understand the logic of only including the user interface (the
lookup)
in the form rather than directly in the table. If you have many
tables in
which there is a "Unit" field. The unit field stores a measurement
unit
(ft,
cm, m, L, gallon, etc.). As nearly every table in the database
contains a
"Unit" field I wanted to create a lookup table in which to store the
appropriate units so that users are forced to enter the unit in a
standard
form. I see that I can do this by using a combo box at the form
level.
But,
is it necessary or good design to create a relationship between the
table
data is being entered into and the input table? I apologize if this
question
isn't worded very well, I'm still learning the terminology.

Jessi

"BruceM" wrote:

Thanks for pointing that out. I did a little more experimenting,
and
found
no problems in sorting by the lookup field when it uses a value
list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep
doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" wrote in message
...
Everything the referenced link says is correct but there really
are
cases
where the Lookups won't cause any harm. Let me try to separate
the
times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key
that is
different from the value you want to display. For example, you
have a
table of department names and each department has an ID. Adding a
lookup
at the table level will cause problems with both nested queries
and
VBA
code due to the confusion between the departmentID and the
departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you
want to
display. An example might be a State code table. This won't work
if
you
want to display the StateName. In that case the key field would
be
different from the display field.
2. You have a small list of values and the value you display is
the
value
you want to save. An example might be Male, Female, Unknown. If
the
value you want to save is a code - M, F, U then you should not use
a
table
level lookup. Keep in mind that combos and listboxes work fine
with
text
values but you need numeric values for option groups so if you
want to
use
an option group on a form, you'll have to work around the
numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are
not
using lookup value tables, you need to be very careful to keep
multiple
instances of combos in sync. You will keep a validation rule at
the
table
level to ensure that bad data cannot be entered and use combos on
forms to
provide pick lists but you will be responsible for making the same
changes
to the table field's validation rule property and all combos based
on
that
list. The next version of Access will take care of this little
problem
of
keeping the value list and the combos in sync but the current and
older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and
a
table
that I add to all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables,
however
could
someone please explain how you avoid using them - is the answer
to
have
many
tiny tables with potentially only 2 fields (assuming it is
necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to
have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create a
table for this.

Many thanks

Helen

Yeah, this particular database is for storing/retreiving/analyzing
groundwater data. I wish there were more examples out there for designing
this kind of database but they are usually more geared toward business use.

Anyway, I tried adding the units table to the relation window again and that
seems to work fine (I can create both relationships and enforce referential
integrity). I just didn't know if there was something "wrong" with adding
the table to the window more than once. As far as the primary key is
concerned, I too have been thinking about whether I want to use the unit for
the primary key. Its an ongoing debate and I'll have to read more to make
that decision. That's for your useful comments/suggestions.

Jessi

"Evi" wrote:

I can't comment on your table structure - I've no experience in this kind of
db but I can see why you wanted all those unit fields now.

Does it tell you why you can't re-inforce ref integ?
If it's because you want to link the table twice, then just add the units
table a second time to the relationship window.

I suspect that it is because you have made the Unit Abbreviation the Primary
Key field of the Units Table but someone has not used your current
convention (eg they may have put a space in front of the l or written cms
instead of cm).
We can spot the faulty entries by using a Find Unmatched Query to find
anything in your main table's units field that isn't in your Units table.
You can then edit those entries by hand

Switch on Name Autocorrect, just for now. (switch it off when you've
finished everything)
I'm not sure that it is a good idea to use the Unit Abbreviation field as
the Primary Key field (someone else can confirm or deny this). Autonumbers
are safer because they can't be edited at all. They look ugly in tables but
the user need never see them. The combo boxes will contain them in their
first column but the wizard will encourage you to hide this so that the user
will only see the second column with the actual letters.
Lets say you add an Autonumber field to your Units table (call it UnitID)

To start with, add a number field to your Main Input table, never mind what
you call it, lets say also UnitID. In the end you can rename it by the same
name as your original field if you have to.

Once you have used your FindUnmatched table to check that both table have
the same data in the MeasurementUnit field then you can put both tables into
a query window and join them by the Measurement unit field (ie not the ID
field)

Check that the Input table still has the same number of records as before
you joined them. Put the letter fields from both tables into your query grid
and check if they have the same letters in them. (a fairly quick glance at
an example of each unit should do the trick)
Remove the other fields. Change the query to an Update Query. Add the UnitId
field from your Main table.
In the Update To line put

tblMeasurementUnits!UnitID

Do the same for the other UnitID type fields in your other tables.

Because the tables are linked, the UnitID field in your main table will now
have the correct ID number so that if it is added to a query with the
UnitAbbreviation field from the Measurements table you will see the correct
measurement.
Change the query back to a Select query and add fields and in the query
window to and check that all the fields in your main table are now filled.
then you can decide if you want to rename your Main Table's UnitID Field to
the name of your letter field.

Evi



"Jessi" wrote in message
...
Thank you for the reply Evi.

I thought I would provide a more concrete example.

tblWellCasingDetails
StationID (pk)
ComponentID (pk)
ComponentDescription
InnerDiameter
InnerDiameterUnit
TopZoneDepth
BottomZoneDepth
DepthUnit
DepthReference
Comments

tblHorizontalSurveyData
StationID (pk)
SurveyDate
SurveyGeneration (pk)
CurrentSurvey
SurveyCompany
XCoordinate
YCoordinate
CoordinateUnit
CoordinateSystem
HorizontalDatum
Notes

tblMeasurementUnits
Unit (pk)
UnitFullName
MeasurementSystem
MeasurementType

The first two tables above both contain fields which require an input for
the "Unit". In fact, tbl:WellCasingDetails has two fields which require
an
input for the "Unit" (InnerDiameterUnit and DepthUnit). The table
tbl:MeasurementUnits is meant to serve as a look up table for the two
tables
above and several others. I can create a relationship between
tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential
integrity. However, I run into a problem when I try to create a
relationship
from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to
the
tbl:MeasurementUnits. I can create the relationships, but cannot enforce
referential integrity. Any suggestions?

Jessi



"Evi" wrote:

.. Putting the units in another table is an excellent idea.

It is indeed good design to create a relationship. However, if you add a
field to an existing table, you may need to run a series of Update
Queries
to get you started before you join up the tables and 'Enforce
Referential
Integrity'

You may even need to update some of the records by hand (using your
combo
box in a form of course) before you can join your tables in the
Relationships Window. (I've even had to resort to calling one of the
items
in my 'lookup' table 'Unknown' because the unit that the inputter used
could
have been almost anything. But this just highlighted the necessity for
the
table)

You can add a 'lookup' table more than once to the relationship window
or in
a query grid so it can join to more than one table but I have a sinking
feeling that you may have a design problem when you say that you have
many
tables in which there is a Unit field - but then maybe I'm being
unnecessarily gloomy.

Evi



"Jessi" wrote in message
...
My question is related to the whole discussion of "lookup evils".

I now understand the logic of only including the user interface (the
lookup)
in the form rather than directly in the table. If you have many
tables in
which there is a "Unit" field. The unit field stores a measurement
unit
(ft,
cm, m, L, gallon, etc.). As nearly every table in the database
contains a
"Unit" field I wanted to create a lookup table in which to store the
appropriate units so that users are forced to enter the unit in a
standard
form. I see that I can do this by using a combo box at the form
level.
But,
is it necessary or good design to create a relationship between the
table
data is being entered into and the input table? I apologize if this
question
isn't worded very well, I'm still learning the terminology.

Jessi

"BruceM" wrote:

Thanks for pointing that out. I did a little more experimenting,
and
found
no problems in sorting by the lookup field when it uses a value
list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep
doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" wrote in message
...
Everything the referenced link says is correct but there really
are
cases
where the Lookups won't cause any harm. Let me try to separate
the
times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key
that is
different from the value you want to display. For example, you
have a
table of department names and each department has an ID. Adding a
lookup
at the table level will cause problems with both nested queries
and
VBA
code due to the confusion between the departmentID and the
departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you
want to
display. An example might be a State code table. This won't work
if
you
want to display the StateName. In that case the key field would
be
different from the display field.
2. You have a small list of values and the value you display is
the
value
you want to save. An example might be Male, Female, Unknown. If
the
value you want to save is a code - M, F, U then you should not use
a
table
level lookup. Keep in mind that combos and listboxes work fine
with
text
values but you need numeric values for option groups so if you
want to
use
an option group on a form, you'll have to work around the
numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are
not
using lookup value tables, you need to be very careful to keep
multiple
instances of combos in sync. You will keep a validation rule at
the
table
level to ensure that bad data cannot be entered and use combos on
forms to
provide pick lists but you will be responsible for making the same
changes
to the table field's validation rule property and all combos based
on
that
list. The next version of Access will take care of this little
problem
of
keeping the value list and the combos in sync but the current and
older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and
a
table
that I add to all my applications.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables,
however
could
someone please explain how you avoid using them - is the answer
to
have
many
tiny tables with potentially only 2 fields (assuming it is
necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to
have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create a
table for this.

Many thanks

Helen