Lookup Evils

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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/
 
HelenJ said:
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.
Click to expand...

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.
 
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.
 
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
 
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.
 
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
 
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
 
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.
 
Thanks Bruce - very comprehensive!

BruceM said:
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
Click to expand...
 
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.
Click to expand...

Just to clarify... don't make the common mistake of equating a Primary
Key with an Autonumber. Access seems to make you want to think they
are the same thing but they are NOT.

For instance, a table of States could and should have the two-letter
state code as its Primary Key, and that is the value you could store
in your Address table. You might want to have the full state name (AK
= Alaska say) in the States table as well. However, if you have a
table of (say) ProjectStatus, it could have just one field, the status
itself ("Proposed", "Approved", "Initiated", ...) as its Primary Key.
If you do so just set the Relationships between the status table and
the other tables using the status to Cascade Updates, so that if you
need to change "Initiated" to "In Progress" the change will apply
everywhere the field is used.
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!
Click to expand...

Just don't use the lookup wizard AT ALL. On the Form toolbar, there is
a Combo Box wizard. This gives you the same choices - type in a value
list, or select an (already existing) lookup table.

John W. Vinson[MVP]
 
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.
 
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.
Click to expand...

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.
 
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.
 
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
 
.. 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
 
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
 
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
 
Back
Top