Using Lookup fields

D

DumbWithData

Is it bad design to use the lookup field data value in tables? If so, why?
Is it better to use these in forms?
 
B

Brad

So I'm designing my first database. When I'm setting up the tables, would it
be better to leave a column designated as textbox and limit what a user can
enter via a listbox on the form or go ahead and set the column in the table
as a listbox?
 
A

Albert D. Kallal

Brad said:
So I'm designing my first database. When I'm setting up the tables, would
it
be better to leave a column designated as textbox and limit what a user
can
enter via a listbox on the form or go ahead and set the column in the
table
as a listbox?
Click to expand...

use the wizard to build a listbox, or combo box on the form. This will allow
you to limit the options for a user...

here is a post of mine explaining the lookup issue:

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!

So, I going state that as long as they work for you, the more power
to you!

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). 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). If you had used a query in the first place, then
in your code, both the ID, and the description would be available
WITHOUT any extra coding!

Further, lets say later on we decide add another field to our fruits table.
Lets say we need to add the color 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 color.

Now, go back to those reports and drop in the color field beside the fruit
text! OOPS, cant do that!! Of course, the fruit name is a lookup Value.
However, to display the fruit name AND THE color, then all of those
queries, reports etc. will now need to be modified to display the color of
the fruit. And of course, to bring in this additional field of color, 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 (two) 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 avoid them. Further, avoiding
them makes you learn the correct skills for other sql database systems you
will use.
 
F

Fred

Thanks for taking the time to write such an exceedingly informative and
detailed explanation.
 
D

David W. Fenton

So I'm designing my first database. When I'm setting up the
tables, would it be better to leave a column designated as textbox
and limit what a user can enter via a listbox on the form or go
ahead and set the column in the table as a listbox?
Click to expand...

If you're using a table to store the choices, you should establish
referential integrity, such that only the values from that table can
be stored in the field. Then you won't run the risk of someone
putting invalid data into the field should they somehow gain access
to it outside your forms.
 
D

DumbWithData

One more question on this. Can one simply remove the lookups that one has
previously put in tables?
 
J

John W. Vinson

One more question on this. Can one simply remove the lookups that one has
previously put in tables?
Click to expand...

Yes. Open the table in design view; select the field. Choose the Lookup tab in
the field properties box on the lower left; change the lookup from "Combo Box"
to "Textbox".
 
J

Jeanette Cunningham

typogarphical - sounds like a new species of dinosaur or shark?

Jeanette Cunningham
 
T

troy23

It is fine to use them and as long as your database follows relational
design there should not be any issues. Instead of having lots of
lookup tables it is possible to put all lookups into one table.

For FREE Access ebook and videos click here
http://access-databases.com/ebook
 
K

Keith Wilby

troy23 said:
It is fine to use them and as long as your database follows relational
design there should not be any issues. Instead of having lots of
lookup tables it is possible to put all lookups into one table.
Click to expand...

And your comments on lookup *fields*? And please don't spam this NG.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Appending tables 0
Creating Network Diagram using a Chart SW integration 0
Setting column width in a table with a macro is not working 0
Word Template where user inputs <TITLE> and then that can be displayed in footers or headers [MAC] 0
comparing table values 1
Lookup Fields 2
Lookup fields in tables bad? 22
Sorting Multiple Tables 1

Top