You want to be "clear" on what we mean when you should avoid lookup fields.
First, they are not really that bad, but when you run into trouble with
them, you will find that you begin to dislike then very much!
Now, lets clearly define what those developers are talking about!
Use of the lookup wizard feature at the TABLE DESIGN level
I repeat: lookup feature at the TABLE design level is the thing to avoid.
In other words, you are encouraged to use the combo box wizard in a form.
You are even ok to use the lookup wizard when you build a query (often, many
people don't realize that the lookup wizard also works when you build a
query!). However, since you are in the query builder, it is probably much
nicer to draw a join line, and use the relation ability of the query builder
to pull in that lookup value you need to lookup via a join. (ie: it is much
clear and better to simply drop in the additional tables into the query
builder
instead of using lookups).
(more on this in second)..
So, as a general concept of database designs, you encouraged to pull data
from tables all the time. This idea of pulling values from other tables is
the very essence of a relational database. So, yes, use good designs that
encourage values to be looked up from other tables (but, avoid that lookup
feature at the table level). I repeat, we are talking about this feature
ONLY
at the table design level. You build and design tables in a database. You
then join these tables together. However, each table should have it fields,
and when you need to lookup values in other tables, you use sql to do this.
So, lots of relations, and looking up values is encouraged in your designs.
However, at the table level, there is few problems as to why the lookup
wizard feature is bad:
The often quoted reason is that then the novice user will NOT know what is
going on! Golly, that is not the best argument here! After all, we can drive
our car to work, and we don't know what the motor does, or even what it
looks like! That don't mean we should not drive!
I mean, the look up feature is there to help users, and make things easy.
However, here is some REAL pit falls:
First, in any good application design, you will RARELY be editing the data
in the tables directly. So, most of the time, we are talking bout a
continues form, or a standard data entry form. This means the benefits of
the lookup are very much reduced. (in those forms, you can quickly build the
combo box with the combo box wizard, and this is most certainly encouraged).
Anyway, just try and build a simple report based on that table with a lookup
field. The first problem that arises is that the text box on the report will
appear by default as a combo box. That don't look very good. Worse, is now
try and use the sorting and grouping options in the report. You get the
weird situation where in the sorting and grouping option, your report will
sort by the ID value, but display by the look up value! This is sheer
confusing, and it don't make sense.
You will say, golly, why does this thing not sort by the value it displays?
Worse, is how do you in fact sort by the actual looked up value? I mean if
we have a simple list of fruits in a look up table, we probably will want to
sort by the looked value! The problem is that you can NOT sort by the look
up value! The end result is you dump the table lookup feature, and then have
to build a query and join in the actual text value of the fruit from that
table into the reports query anyway!. You then can sort on that! (wow...that
built in lookup feature did not save one bit of work here...did it!).
So, your report will sort by the id value, and displays the by lookup value
unless you dump the use of the lookup. Yuk!
What if you load the data into a reocrdset, then again, what do you get for
the lookup value field? (you get the id value, and again, if you need to get
the value from the other table, you wind up creating a query again! (or
write some code to grab the text value in the lookup table). So, once again,
in code with reocrdsets, the table lookup feature does nothing, and again
tends to surprise the developer as to what value will be returned!. Once
again, you don't save any coding by using this table lookup do you? (it is
ignored in reocrdsets)
Further, lets say later on we decide add another field to our fruits table.
Lets say we need to add the colour of the fruit. You have to realize that
over time, database do change a lot! You must *design* for change!
So, now we just add an additional field to the fruits table called colour.
Now, go back to those reports and drop in the colour field beside the fruit
text! OOPS, can't do that!! Of course, the fruit name is a lookup Value.
However, to display the fruit name AND THE colour, then all of those
queries, reports etc. will now need to be modified to display the colour of
the fruit. And of course, to bring in this additional field of colour, we
CAN NOT use the table lookup feature.
Of course, what is really is the problem here is that will have to build
queries to do this! Something we should have done in the first place!!!!
Now, you got a report with both lookups and a join to this other table.
Well, golly, you might as well dump the lookup, since you have to bring in
the fields from the fruits table via sql joins anyway! If you had built
queries in the first place, then you would just simply plop in this extra
field into the report, and you are done. With the table lookup approach, you
are now stopping in the 12 places in the database, and now building the
queries, or even changing the report source from a table to query. This work
really starts to add up, and bogs you down because of poor design choice in
the first place. We should be able to add this additional field, and not all
of sudden stop dead in our tracks to build queries everywhere.
Worse, it gets messy, as all of a sudden you have to decide if you want dump
the table lookup, as it does not make sense to have both a look up field,
and then some additional fields from that same lookup table brought in via a
sql query join! Yuk!. Worse, after you get knee deep into the project, you
CAN NOT safety remove the lookup field, as then you might introduce bugs and
will have to hunt down every single spot (form, report etc) in the database
that relied on the table lookup feature (that you are now trying to
remove!).
!
So, there is some frustrations in using the lookup. However, if you have not
run into the above problems, then it is most un-fair of me say don't use
them. There is tons of users out there that use and enjoy, and even find
increased productivity from using the table lookup feature. I have to say
that your mileage will vary on this one.
However, due to problems like the above, I do advoid them. Futher, advoiding
them makes you learn the correct skills for other sql database systems you
will use.
When you build a form, feel to use the combo box wizard to lookup
values as often as you want, and this is a good and recommended
practice.
So, to "bring in" other additional fields from those other tables, you
simply fire up the query builder.
If you have 5 other tables that are used for these lookups, then simply drop
in those additional 5 tables.
You now of course have to draw the join lines from the "main" table to these
other tables with the information you need.
So, draw the join lines, and then double click on the join line, and make
the join a left join (select the option to always include the main table
records...and possible a child record).
Now, you are free to drop into the query builder any field, and even those
"description" fields etc from those other tables. Those field can no be used
in the report..and sorted on correctly.
So, there is no reason to use the lookup field feature in a form, as you can
just use the combo box wizard.
And, for reports..just fire up the query builder and start dropping in all
those tables as above..and again you don't need the lookup feature. And, you
will find that there is NO confusing as to if the ID of the lookup, or
description will show, and no surprises in terms of sorting etc.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com http://www.attcanada.net/~kallal.msn 
