Lookup tables

[Guest]

What exactly is a lokup table and when to use it.

Emilio

 

[Arvin Meyer [MVP]]

What exactly is a lokup table and when to use it.

A lookup table (as opposed to a lookup field) is a table that holds values
which are the descriptive values for a field in another table. A typical
example is the Categories table in the Northwind sample database that comes
with Access. If you look in the Products table, in that same database, you
will see a lookup field (Category). It is better to show the CategoryID (the
actual value) than the text from another table. Tables are, for the most
part, not to be used for, for directly viewing or entering data. Access is
one of the very few DBMS that even makes it possible for a user to view a
table. A query can be used to join the data, and any form (or report) even a
datasheet form can be used to display it. This web page should explain why
to use lookup tables, but not lookup fields:

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Garret]

I am confused as to what the difference is between a lookup table and a
lookup field. I thought a lookup table is a table where another table
that has the lookup field draws its values from. In my database, I
have a field called [TypeofGage] in the [Gages] table. That field is a
lookup field which gets its values from the table called [GageTypes] in
the primary field called [GageType]. What is wrong with this, and what
is the alternate way to do the same thing?

 

[Douglas J Steele]

See http://www.mvps.org/access/lookupfields.htm at "The Access Web" for
what's wrong with it.

The alternative is to use a form with a combobox control. Since you should
never be updating tables directly, this shouldn't make any difference to
your users.

 

[Garret]

Yes I've seen the "Evils of Lookup Fields".
I have to have the lookup field as part of my table though, because I
need that field to do searches on (to see which Gages belong to which
GageTypes). The SQL for the field row source is:

SELECT [tblGageTypes].[GageType] FROM tblGageTypes ORDER BY [GageType];

You probably already knew this, but I don't see where any errors would
happen. Also, am I misunderstanding something, or are Lookup tables ok
but lookup fields not? I thought you needed one to have the other...

 

[Douglas J Steele]

You need a lookup table to use a lookup field. You do not have to use lookup
fields to use lookup tables (and, as you've seen, most of us recommend
strongly against lookup fields)

The only reason I can see that you'd need a lookup field is to simplify data
entry directly into the table. Since you shouldn't be doing data entry
directly into the table, I don't see the point of lookup fields.

 

[Garret]

Quite the opposite I think Doug - I see the lookup fields as necessary
indeed. I'll try to explain simply. In my case, I have Gages. Each
Gage has a field called GageType, of which the only possiblilites of
the GageTypes are the the GageTypes in the table called GageTypes.
Users may be adding new GageTypes (new records in the GageType table),
so the field will need to grow (and display more choices when adding
records for this field). Also in the table GageTypes, there is a
relationship so that Gages is a subtable (this way, the user can see
which Gages belong to each GageType). Users will also be looking at
Gages, and should be able to see which Gagetype the Gage belongs
to...so it works both ways.
I don't know if you are familiar with the Mountain Animal Hospital
Database that comes with Access 2000 Bible, but there is much of the
similiar relationship in the Animals table, except that the Type of
Animal is not a lookup field in the Pets table - thats the only
difference, and I don't see any harm if the Type of Animal field were
to be made a lookup field for the Animals table.

 

[Garret]

I think I'm getting something wrong here. Let me see if I have this
correct. A lookup field is a field where the only possible values in
it are records from another table (exactly like a combo box). I'll try
to explain my situation of why I need the lookup field.
In one table, I have Gages. Each Gage has a GageType. The only
possible GageTypes are the ones in the tblGageTypes. New GageTypes may
be added from time to time to the tblGageTypes, and therefore opens up
the possibility of a Gage being of that Type, you see? Also, the
tblGage is the child of tblGageTypes - so that each GageType can be
broken down to see all the Gages of that type (for faster searching
purposes). The tblGage needs the GageType field too, so that users can
look at a Gage and see what Type it is?
I don't know how familiar you are, but in the Mountain Animal Hospital
Database that comes with Access Bible 2000, there is a very similar
relationship with Pets and Animals. There is the Animals table that is
broken down so you can see which Pets are of which Animal type (exactly
like my Gage:GageType relationship). Except in the pets table, there
is no lookup field (when entering into the Type of Animal field, there
is no "combobox" of limited possibilities from the Animals table). I
don't see any harm coming even if it were this way?
I hope I've been clear. Thanks.

 

[Douglas J Steele]

You seem to be ignoring my point.

On your form, create a combobox that's bound to the Gage field and set it up
so that the user must select from the combobox (as opposed to be able to add
new entries). Gage doesn't need to be a lookup field then. Have a
relationship between the two tables, with RI turned on, and it won't be
possible to use an incorrect value.

If you're updating through the table, stop it! You should never update
tables directly.

 

[Garret]

No, I haven't been ignoring you. I realize that is a possibility
because I've done that in other instances. I will try this but it
seems like I will run into trouble down the line when I do searching
processes.

Users will never enter data through the tables, only forms. Fear not!
Only me (as the programmer) enters data into the tables sometimes.

 

[George Nicholson]

The problem lies in how Access implemented Lookup Fields. You are better off
rolling-your-own rather than relying on the "shortcut" Microsoft provided.

In place of a lookup field:
Create a stored query with the SQL you indicated, set your field to combobox
and use the query as the recordsource. The combobox setup will propogate to
any forms you add the field and you can adjust what the query contains &
what the combo displays at any time while avoiding the "evils" of lookup
fields.

 

[Garret]

So basically instead of using the SQL in the field's rowsource, I
should use that SQL in a query, and set the rowsource to the query
instead?

 

[Albert D.Kallal]

No, I haven't been ignoring you. I realize that is a possibility
because I've done that in other instances. I will try this but it
seems like I will run into trouble down the line when I do searching
processes.

Why would searching be a problem? Would you build a query that joins in the
"descriptive" values from the other table, and use that as the data source
for you form, or "search form" as you state? Of course no one will know (or
even care) what the id is, and you obviously want to search by the text
description. So, just build a sql query, and if you got 1, 2, or 5 lookup
fields, then simply add those tables to the query, and drop in the
description fields as a.

I'll repost some text of mine as why lookup fields can be a problem (as
mentioned, if you need the text...build a query, and then use that query for
the search, form, report or whatever).

--------

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)

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
now must add a new lookup field, or build a query.

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! (it is FAR FAR easier
to simply drag and drop fields into the query grid then it is to make a
bunch
of lookup fields in a table. Worse, as mentioned, the sorting and grouping
is now messed up (what is you DO want to sort on the id, and not the
text description. What if you do DO want to sort on the DESCRIPTION
and not the id?

It is easy to create one lookup field, but as above shows, often you need 2,
3 or
MORE fields from that related table. Further, when you go into design mode,
or
print out your table structures..what fields actually belong to what table?
If I have
2, 3 or more lookup fields to the same table, then my table fields are NOT
actual fields, but 2, 3 or 4 of those fields are simply lookups.

Further, what happens if you DO want to display the actual id used, then
with a lookup
you don't see the actual id (this often the case for testing, and
debugging...you need to actual
record id).

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. The fact
for the FIRST lookup field you did not build a query is fine. However, the
instant you need MORE then one field from that table, you now are going
around building queries...or adding more lookup fields (which as I
mentioned don't sort correctly anyway).

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.

 

[Albert D.Kallal]

So basically instead of using the SQL in the field's rowsource, I
should use that SQL in a query, and set the rowsource to the query
instead?

Yes, but it depends on where, and how you need to use the data.

If you are talking about a form, then use the combo box wizard, and you
don't even have to write any special sql, or change the sql used for the
form. You will not need a lookup field, as you simply use the comb box
wizard.

However, if you need to be able to search, or use the text data as if it was
a field (say in reports, or some searching routine), then you would not use
a combo box wizard, but would like the looked up values to appear as a
standard column To do this, you simply replace the data source of the form
(or whatever) to a sql query, and drop in the lookup table into that query.
(thus, if you need more then ONE lookup value from that table, you can drop
in as many fields as you like from that other table).

You can read my other post as to the reason why many of us advoid the field
lookup feature...

 

[Garret]

I think anycase where hassle would result does not apply in this
particular case of mine. In my lookup table, the FieldID is the actual
field where the lookup field would be drawing from, and only that
field. So there is no hiding of IDs and showing of other fields. (I
am not sure, but when you say more than 1 field, you mean something
like:

SELECT [tblGageTypes].[GageType], [tblGageTypes].[Description],
[tblGageTypes].[Notes] FROM tblGageTypes ORDER BY [GageType];

where more than one field would be used. This way, the combo box would
have several categories in it. So instead of just (your example)
Fruit, there would be Fruit and then another column with Colour. Or
did you mean that there was another lookup field called colour based on
the same table?

Whatever the case may be, I think I'll heed your advice and either
1. Use the query as the rowsource instead of the SQL
2. Use a combo box and just put the query/SQL statement in that's
rowsource

....all while leaving the used-to-be lookupfield as a regular text field
with no strings attatched.

 

[Albert D.Kallal]

where more than one field would be used. This way, the combo box would
have several categories in it. So instead of just (your example)
Fruit, there would be Fruit and then another column with Colour.

Yes...I meant simply another column is needed.....

Or
did you mean that there was another lookup field called colour based on
the same table?

Well, that is just it, as you start need SEVERAL field values from that
other table, then lookup fields really starts to get messy.

...all while leaving the used-to-be lookupfield as a regular text field
with no strings attatched.

As I state in the other post, many people do use, and find the lookup fields
a ok feature. I can't really stand here and say don't use something you want
to use!

It really is only after a certain amount of time, and certain types of
development will you find the feature a problem. Lets face it, MS put that
feature there to make things easy for people, and for many, it does help. If
you don't run into problems, then , really, use these features.....and be
happy!!