Lookup Evils

[HelenJ]

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

 

[Jeff Boyce]

Lookup fields, not lookup tables.

If you are fairly certain you will never need to expand the list of possible
values beyond two records, you can use a Value List row source in the combo
(or list) box on your form. A reminder: Access tables store data, Access
forms display it (and allow for data entry/edit).

.... and the first time you go back to modify a combo box's value list
because you/your customer added a third choice, you'll reconsider using a
table!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Rick Brandt]

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.

You can use embedded ValueLists for these if you like, just define them in the
ComboBoxes and ListBoxes on your forms, rather than in your tables.

 

[BruceM]

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]

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

 

[Pat Hartman\(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.

 

[BruceM]

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.

 

[Jessi]

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

 

[Evi]

.. 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]

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]

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]

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

 

[BruceM]

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]

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]

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]

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

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.

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.