Lookup Evils

When you say you "can create a relationship between tblMeasurementUnits and
tblHorizontalSurveyData and enforce referential integrity," what exactly do
you mean? I can guess that the relationship is between CoordinateUnit and
Unit, but to what end? Some sample data may help.
Are you using a compound PK in the first two tables? I would guess that
StationID has something to do with a Station table you haven't mentioned.
If so, it should be a foreign key in tblWellCasingDetails and in
tblHorizontalSurveyData, not the PK.
If WellCasingDetails are details for a particular station, does that mean
there could be any number of details for a station. That is, is the
situation one station many well casing details? Remember that a PK
uniquely identifies the record. It could be a single field or a combination
of fields. If it is a single field it could be something arbitrary such as
autonumber, or it could be a unique numbering system your company uses.
Invoice number and quote numbers are typical examples of the latter. If it
is a combination of field, you must be very careful to assure the
combination is truly unique.
You have provided information about the tables, but it may help to back up
and describe something of the real-world situation behind the database.


"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

Yes, some background information about the real world situation would be
useful.

I'm a PhD student in hydrogeology and as a result I conduct groundwater
research. Most of the data I collect from my research comes from boreholes
(holes drilled in the ground) and later from the wells installed in those
boreholes. Each borehole is a station (so yes, there is a
tblStationDetails). Each borehole/well is uniquely named. I've been working
on learning how to design/build a database for the site data (which spans
over 20 years) because historically the data has been stored/manipulated in
excel spreadsheets (big nightmare). I would like other students and
consultants, most of whom will have limited to no experience with databases
and won't necessarily be familiar with all the different types of data, to be
able to input data into the database and extract data from it while
maintaining he integrity of the data.

I collect many different types of data from each borehole. Two types of
data collected for each borehole are the survey coordinates for the borehole
(tblHorizontalSurveyData ) and details about the steel casing installed in
each borehole (tblWellCasingDetails). The survey coordinates for the
borehole can be measured in several different units depending on who did the
survey (typically in feet or meters). The details regarding the steel casing
installed in each borehole include the top and bottom depth of each piece of
casing (can be multiple pieces in each borehole) and the diameter of the
casing. The depths are typically measured in several different units (feet
or meters) and the diameters can be given in several different units (inches
or centimeters).

So, I want to make sure that users entering this data have to choose from a
list of units rather than entering the unit into a text box where they can
potentially use many different forms (abbreviated form, full form, etc.).
Its important that the form for the unit for each piece of data be consistent
because subsequent queries will convert the data into the units desired for
different types of reports (when we report to the site owner we use imperial
units and when we write for scientific journals we use metric units).

I gathered from the previous discussion that building the constraint that
users pick from a list of possible unit values is best done at the form level
rather than the table level. The question that I had was is it
necessary/good design to create a relationship between the unit field in the
tblMeasurementUnits (pk) and to the unit field in the tblHorizontalSurveyData
(fk), for example. Because each piece of data (record) in many different
tables has an associated unit many relationships would need to be created
between each table and the tblMeasurementUnits. Or if its acceptable to
implement that constaint in the combo box on a form by choosing "limit to
list" in the combo box properties menu. Or, by choosing "limit to list" in
the combo box properties menue is Access actually creating that relationship
for me?

I'll try to provide some answers to your questions about the primary keys.
For the tblWellCasingDetails the primary key is a compound key composed of
the StationID field and the ComponentID field. This is necessary because the
component ID identifies a type of component (steel casing, PVC casing, etc)
and as such will be repeated for many stations. The same is true for the
tblHorizontalSurveyData (compound primary key made up of the StationID field
and the SurveyGeneration field). The tblMeasurementUnits has a simple
primary key of the field Units. I'm fairly certain the relationship between
the Units field in the tblMeasurementUnits and all the other tables is one to
many. The Units field from the tblMeasurementUnits then shows up in the
other tables as a foreign key.

I hope this clarifies things. I'm still learning the vocabulary. I'm
primarily interested in good design princple and whether that includes
creating the relationships I described above to force the user to enter
particular values stored in a table or whether that can just be set as a
property of the combo box in the input form or if they end up being the same
thing?

Thank you for your help and time.



"BruceM" wrote:

When you say you "can create a relationship between tblMeasurementUnits and
tblHorizontalSurveyData and enforce referential integrity," what exactly do
you mean? I can guess that the relationship is between CoordinateUnit and
Unit, but to what end? Some sample data may help.
Are you using a compound PK in the first two tables? I would guess that
StationID has something to do with a Station table you haven't mentioned.
If so, it should be a foreign key in tblWellCasingDetails and in
tblHorizontalSurveyData, not the PK.
If WellCasingDetails are details for a particular station, does that mean
there could be any number of details for a station. That is, is the
situation one station many well casing details? Remember that a PK
uniquely identifies the record. It could be a single field or a combination
of fields. If it is a single field it could be something arbitrary such as
autonumber, or it could be a unique numbering system your company uses.
Invoice number and quote numbers are typical examples of the latter. If it
is a combination of field, you must be very careful to assure the
combination is truly unique.
You have provided information about the tables, but it may help to back up
and describe something of the real-world situation behind the database.


"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

As I understand, the top level of the structure is the station. The table
may be something such as:

tblStation
StationID (primary key; could be the unique name)
Location
BoreholeWell (Borehole or well? Could be Y/N, I suppose.)
BoreholeDate
WellDate
SurveyDate
Surveyor
etc.

You mention tblStationDetails, but I don't see how that fits in, if indeed
it is different from the tblStation I have suggested.

You mention survey coordinates. Since you have used the plural, there must
be more than one. Assuming there is one survey, and that a survey has a
variable number of coordinates, you would need something like:

tblHorizontalSurveyData
SurveyID (primary key)
StationID (foreign key; linked to StationID in tblStation)
Coordinate
CoordinateUnit (meters, feet, etc.)
etc.

I don't know how a coordinate is determined. You may need several pieces of
information (x, y, and z axis, or something).

If there can be several surveys, each survey needs its own record:

tblSurvey
SurveyID (PK)
SurveyDate
Surveyor
etc.

Note that SurveyDate and Surveyor would be removed from tblStation in this
scenario. tblHorizontalSurveyData would be related to this table rather
than to tblStation:

tblHorizontalSurveyData
HSDataID (primary key)
SurveyID (foreign key; linked to SurveyID in tblSurvey)
Coordinate
CoordinateUnit (meters, feet, etc.)
etc.

tblWellCasingDetails may be something such as:

tblWellCasingDetails
CasingDetailsID (PK)
StationID (FK to tblStation)
SectionNumber
TopDepth
BottomDepth
DepthUnit (feet, meters, etc.)
Diameter
DiameterUnit (inches, centimeters)

It could be that there is a need for another level, similar to the second
choice I suggested for the Survey.

You may be overthinking the problem with measurement units. I don't think
you need relationships to that table. If you have a Units table at all I
expect it would be a lookup table (not to be confused with a lookup field).
However, I would think a value list as the combo box row source may be all
you need. If the row source type is Value List, the Row Source may be:
inches;centimeters

When it comes time to generate a report, you could have something like this
as a field in the record source query:

ConvertToCentimeter: IIf([DiameterUnit] = "inches",[Diameter] *
2.54,[Diameter])
and
ConvertToInches: IIf([DiameterUnit] = "centimeters",[Diameter] *
..39,[Diameter])

If you have a large number of units (inches, millimeters, centimeters,
microns, cubits, etc.) you could use a lookup table instead of a value list.
In that case Limit To List would be adequate. In any case you could have
something like this as the After Update event of the combo box:
If Nz(Me.cboUnit,"") = "" Then
MsgBox "You need to select a unit"
Me.txtDiameter.Enabled = False
Else
Me.txtDiameter.Enabled = Ture
End If

txtDiameter is the text box bound to Diameter.

"Jessi" wrote in message
...
Yes, some background information about the real world situation would be
useful.

I'm a PhD student in hydrogeology and as a result I conduct groundwater
research. Most of the data I collect from my research comes from
boreholes
(holes drilled in the ground) and later from the wells installed in those
boreholes. Each borehole is a station (so yes, there is a
tblStationDetails). Each borehole/well is uniquely named. I've been
working
on learning how to design/build a database for the site data (which spans
over 20 years) because historically the data has been stored/manipulated
in
excel spreadsheets (big nightmare). I would like other students and
consultants, most of whom will have limited to no experience with
databases
and won't necessarily be familiar with all the different types of data, to
be
able to input data into the database and extract data from it while
maintaining he integrity of the data.

I collect many different types of data from each borehole. Two types of
data collected for each borehole are the survey coordinates for the
borehole
(tblHorizontalSurveyData ) and details about the steel casing installed in
each borehole (tblWellCasingDetails). The survey coordinates for the
borehole can be measured in several different units depending on who did
the
survey (typically in feet or meters). The details regarding the steel
casing
installed in each borehole include the top and bottom depth of each piece
of
casing (can be multiple pieces in each borehole) and the diameter of the
casing. The depths are typically measured in several different units
(feet
or meters) and the diameters can be given in several different units
(inches
or centimeters).

So, I want to make sure that users entering this data have to choose from
a
list of units rather than entering the unit into a text box where they can
potentially use many different forms (abbreviated form, full form, etc.).
Its important that the form for the unit for each piece of data be
consistent
because subsequent queries will convert the data into the units desired
for
different types of reports (when we report to the site owner we use
imperial
units and when we write for scientific journals we use metric units).

I gathered from the previous discussion that building the constraint that
users pick from a list of possible unit values is best done at the form
level
rather than the table level. The question that I had was is it
necessary/good design to create a relationship between the unit field in
the
tblMeasurementUnits (pk) and to the unit field in the
tblHorizontalSurveyData
(fk), for example. Because each piece of data (record) in many different
tables has an associated unit many relationships would need to be created
between each table and the tblMeasurementUnits. Or if its acceptable to
implement that constaint in the combo box on a form by choosing "limit to
list" in the combo box properties menu. Or, by choosing "limit to list"
in
the combo box properties menue is Access actually creating that
relationship
for me?

I'll try to provide some answers to your questions about the primary keys.
For the tblWellCasingDetails the primary key is a compound key composed of
the StationID field and the ComponentID field. This is necessary because
the
component ID identifies a type of component (steel casing, PVC casing,
etc)
and as such will be repeated for many stations. The same is true for the
tblHorizontalSurveyData (compound primary key made up of the StationID
field
and the SurveyGeneration field). The tblMeasurementUnits has a simple
primary key of the field Units. I'm fairly certain the relationship
between
the Units field in the tblMeasurementUnits and all the other tables is one
to
many. The Units field from the tblMeasurementUnits then shows up in the
other tables as a foreign key.

I hope this clarifies things. I'm still learning the vocabulary. I'm
primarily interested in good design princple and whether that includes
creating the relationships I described above to force the user to enter
particular values stored in a table or whether that can just be set as a
property of the combo box in the input form or if they end up being the
same
thing?

Thank you for your help and time.



"BruceM" wrote:

When you say you "can create a relationship between tblMeasurementUnits
and
tblHorizontalSurveyData and enforce referential integrity," what exactly
do
you mean? I can guess that the relationship is between CoordinateUnit
and
Unit, but to what end? Some sample data may help.
Are you using a compound PK in the first two tables? I would guess that
StationID has something to do with a Station table you haven't mentioned.
If so, it should be a foreign key in tblWellCasingDetails and in
tblHorizontalSurveyData, not the PK.
If WellCasingDetails are details for a particular station, does that mean
there could be any number of details for a station. That is, is the
situation one station many well casing details? Remember that a PK
uniquely identifies the record. It could be a single field or a
combination
of fields. If it is a single field it could be something arbitrary such
as
autonumber, or it could be a unique numbering system your company uses.
Invoice number and quote numbers are typical examples of the latter. If
it
is a combination of field, you must be very careful to assure the
combination is truly unique.
You have provided information about the tables, but it may help to back
up
and describe something of the real-world situation behind the database.


"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

I think I've got my situation worked out/thought through now thanks to
everyones comments/suggestions. I learn a great deal from all of the
postings and I'm sure other questions will come up in the future as I
continue to work with Access. Thanks again.

Jessi


"BruceM" wrote:

As I understand, the top level of the structure is the station. The table
may be something such as:

tblStation
StationID (primary key; could be the unique name)
Location
BoreholeWell (Borehole or well? Could be Y/N, I suppose.)
BoreholeDate
WellDate
SurveyDate
Surveyor
etc.

You mention tblStationDetails, but I don't see how that fits in, if indeed
it is different from the tblStation I have suggested.

You mention survey coordinates. Since you have used the plural, there must
be more than one. Assuming there is one survey, and that a survey has a
variable number of coordinates, you would need something like:

tblHorizontalSurveyData
SurveyID (primary key)
StationID (foreign key; linked to StationID in tblStation)
Coordinate
CoordinateUnit (meters, feet, etc.)
etc.

I don't know how a coordinate is determined. You may need several pieces of
information (x, y, and z axis, or something).

If there can be several surveys, each survey needs its own record:

tblSurvey
SurveyID (PK)
SurveyDate
Surveyor
etc.

Note that SurveyDate and Surveyor would be removed from tblStation in this
scenario. tblHorizontalSurveyData would be related to this table rather
than to tblStation:

tblHorizontalSurveyData
HSDataID (primary key)
SurveyID (foreign key; linked to SurveyID in tblSurvey)
Coordinate
CoordinateUnit (meters, feet, etc.)
etc.

tblWellCasingDetails may be something such as:

tblWellCasingDetails
CasingDetailsID (PK)
StationID (FK to tblStation)
SectionNumber
TopDepth
BottomDepth
DepthUnit (feet, meters, etc.)
Diameter
DiameterUnit (inches, centimeters)

It could be that there is a need for another level, similar to the second
choice I suggested for the Survey.

You may be overthinking the problem with measurement units. I don't think
you need relationships to that table. If you have a Units table at all I
expect it would be a lookup table (not to be confused with a lookup field).
However, I would think a value list as the combo box row source may be all
you need. If the row source type is Value List, the Row Source may be:
inches;centimeters

When it comes time to generate a report, you could have something like this
as a field in the record source query:

ConvertToCentimeter: IIf([DiameterUnit] = "inches",[Diameter] *
2.54,[Diameter])
and
ConvertToInches: IIf([DiameterUnit] = "centimeters",[Diameter] *
..39,[Diameter])

If you have a large number of units (inches, millimeters, centimeters,
microns, cubits, etc.) you could use a lookup table instead of a value list.
In that case Limit To List would be adequate. In any case you could have
something like this as the After Update event of the combo box:
If Nz(Me.cboUnit,"") = "" Then
MsgBox "You need to select a unit"
Me.txtDiameter.Enabled = False
Else
Me.txtDiameter.Enabled = Ture
End If

txtDiameter is the text box bound to Diameter.

"Jessi" wrote in message
...
Yes, some background information about the real world situation would be
useful.

I'm a PhD student in hydrogeology and as a result I conduct groundwater
research. Most of the data I collect from my research comes from
boreholes
(holes drilled in the ground) and later from the wells installed in those
boreholes. Each borehole is a station (so yes, there is a
tblStationDetails). Each borehole/well is uniquely named. I've been
working
on learning how to design/build a database for the site data (which spans
over 20 years) because historically the data has been stored/manipulated
in
excel spreadsheets (big nightmare). I would like other students and
consultants, most of whom will have limited to no experience with
databases
and won't necessarily be familiar with all the different types of data, to
be
able to input data into the database and extract data from it while
maintaining he integrity of the data.

I collect many different types of data from each borehole. Two types of
data collected for each borehole are the survey coordinates for the
borehole
(tblHorizontalSurveyData ) and details about the steel casing installed in
each borehole (tblWellCasingDetails). The survey coordinates for the
borehole can be measured in several different units depending on who did
the
survey (typically in feet or meters). The details regarding the steel
casing
installed in each borehole include the top and bottom depth of each piece
of
casing (can be multiple pieces in each borehole) and the diameter of the
casing. The depths are typically measured in several different units
(feet
or meters) and the diameters can be given in several different units
(inches
or centimeters).

So, I want to make sure that users entering this data have to choose from
a
list of units rather than entering the unit into a text box where they can
potentially use many different forms (abbreviated form, full form, etc.).
Its important that the form for the unit for each piece of data be
consistent
because subsequent queries will convert the data into the units desired
for
different types of reports (when we report to the site owner we use
imperial
units and when we write for scientific journals we use metric units).

I gathered from the previous discussion that building the constraint that
users pick from a list of possible unit values is best done at the form
level
rather than the table level. The question that I had was is it
necessary/good design to create a relationship between the unit field in
the
tblMeasurementUnits (pk) and to the unit field in the
tblHorizontalSurveyData
(fk), for example. Because each piece of data (record) in many different
tables has an associated unit many relationships would need to be created
between each table and the tblMeasurementUnits. Or if its acceptable to
implement that constaint in the combo box on a form by choosing "limit to
list" in the combo box properties menu. Or, by choosing "limit to list"
in
the combo box properties menue is Access actually creating that
relationship
for me?

I'll try to provide some answers to your questions about the primary keys.
For the tblWellCasingDetails the primary key is a compound key composed of
the StationID field and the ComponentID field. This is necessary because
the
component ID identifies a type of component (steel casing, PVC casing,
etc)
and as such will be repeated for many stations. The same is true for the
tblHorizontalSurveyData (compound primary key made up of the StationID
field
and the SurveyGeneration field). The tblMeasurementUnits has a simple
primary key of the field Units. I'm fairly certain the relationship
between
the Units field in the tblMeasurementUnits and all the other tables is one
to
many. The Units field from the tblMeasurementUnits then shows up in the
other tables as a foreign key.

I hope this clarifies things. I'm still learning the vocabulary. I'm
primarily interested in good design princple and whether that includes
creating the relationships I described above to force the user to enter
particular values stored in a table or whether that can just be set as a
property of the combo box in the input form or if they end up being the
same
thing?

Thank you for your help and time.



"BruceM" wrote:

When you say you "can create a relationship between tblMeasurementUnits
and
tblHorizontalSurveyData and enforce referential integrity," what exactly
do
you mean? I can guess that the relationship is between CoordinateUnit
and
Unit, but to what end? Some sample data may help.
Are you using a compound PK in the first two tables? I would guess that
StationID has something to do with a Station table you haven't mentioned.
If so, it should be a foreign key in tblWellCasingDetails and in
tblHorizontalSurveyData, not the PK.
If WellCasingDetails are details for a particular station, does that mean
there could be any number of details for a station. That is, is the
situation one station many well casing details? Remember that a PK
uniquely identifies the record. It could be a single field or a
combination
of fields. If it is a single field it could be something arbitrary such
as
autonumber, or it could be a unique numbering system your company uses.
Invoice number and quote numbers are typical examples of the latter. If
it
is a combination of field, you must be very careful to assure the
combination is truly unique.
You have provided information about the tables, but it may help to back
up
and describe something of the real-world situation behind the database.


"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