I have read in a number of places that having it is a bad idea to have lookup fields. In most cases, not much rationale is given for the injunction, just the bald statement - "don't do it." Those of you who know me already, know that I am generally not satisfied with stand-alone injunction. I want to know the rationale behind the assertion so that I can then weigh the options and decide for myself whether breaking the rules in a specific case might or might not be a good idea.

So, I would be happy to hear what others have to say about lookup fields, pros and cons, and their experience in using them - good or bad.

Glenn

The only trouble I see is that when you open a table which uses lookup fieds you will see the data from the referenced table, within a combo box and not the "real" code (or key) that is stored in your table.

Somewhere I pick-up this explanation for this topic:

The Evils of Lookup Fields

1. A Lookup field in a table displays the looked-up value. For instance, if a user opens a table datasheet and sees a column of company names, what are in the table are, in fact, a numeric CompanyID, and that ID links the table with a select statement to the company table.

2. Any query that uses that lookup field to sort by that company name won't work. Nor will a query that uses a company name in that field as criteria. If a user creates a combobox to select the company using a value list, the data in the table can be over-written.

3. Another relationship is created which then creates another set of indexes when a Lookup field is created, thus bloating the database unnecessarily.

4. If a combobox based on the lookup is used in a form, and a filter is applied, the persistent filter effect of Access often saves the filter and the next time the form is opened, there will be a prompt for the value (which cannot be provided, thus creating an error).

5. Reports based on the lookup field need a combobox to display the data, causing them to run more slowly. The underlying recordsource can also be modified to include the table, however the index, (unless it was set up within a proper relationship) may not be optimized.

6. Lookup fields mask what is really happening, and hide good relational methodology from the user.

7. The database cannot be properly upsized to, or queried by, another engine (without removing all the lookup fields) because no other engines use or understand them.

8. If security is implemented, permissions to tables are usually denied, and RWOP queries are used for data access. There will often be errors that there are no permissions on a specific table that isn't even being used in a query (because the lookup field is). If the queries are nested or complex, it can take some time to track down the lookup that's causing the error (that is, if it occurs to you).

The Ten Commandments of Access

And it came to pass that the cries and lamentations of the Access newbie's were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spoke, saying: "Nevermore shall the young and innocent wander witless on their journeys!
We shall provide guidance to them, yea, and to all who wish to seek the paths of wisdom." And they caused these commandments to be written and placed before the eyes of those seeking enlightenment.

So heed the words of those who have come before you, and keep these commandments in thine heart as thou dost create thy Database application. If thou shalt only follow these commandments thy burden shall be made light and thy path shall be made straight.

1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.

2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.

3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.

4. Thou shalt write comments in your procedures and explain each variable.

5. Thou shalt understand error handling and use it faithfully in all thy procedures.

6. Thou shalt split thy databases.

7. Thou shalt not use "Autonumber" if the field is meant to have meaning for thy users.

8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.

9. Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.

10. Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do.
Thus spoke the gods of the Database, and blessed be their names! And Blessed, too, are those who contribute to the Access Newsgroup - giving freely of themselves to serve those who hunger and thirst for knowledge and understanding!

Wow, that really hits the nail on the head, doesn't it. There are far more issues than I would have expected. Thanks for passing this along, German.

Glenn

Hi Glenn,

And if all that doesn't do it, they really hit performance with any substantial number of records.

Cheers
Chris

Question to all :

Where then would you use a look up table?

Please advise...

thanks
cOcO

Look-up tables and Look-up fields completely different thing.
You using Look-up tables primary as "static" tables, with "not-changed" frequently information to create "values list", combo-boxes, etc. This way your user don't have to type information, just select from values list and your data more consistent, no mistyping and misspellings, etc.
Look-up fields - may be on the process of design, where you working on the tables level and relationship, then your user don't have even touch a tables, look-up fields don't cost anything.

(Opinion, as usual)

You would use lookup tables in conjuction with combo-box controls on forms. The combo is bound to a field in your main table that stores the value of the key of items selected in the combo box. The net effect for the user is the same as if you had used a lookup field but, as I have found out from this thread, does not have the nasty side-effects outlined in German's earlier response. (Note the distinction between controls, which are members of forms and reports, and fields, which are members of tables and other recordsets.)

Glenn

German & Glenn ,

Thankyou both for your feedback, but I still do not understand the difference.

Let me explain why I say this. For example if I have a look up table that list all the category types for a group of items, the fields in my category table would be categoryID (being the primary key using the autonumber) and categoryType (being the description of the category). In my items table my fields would be itemID, itemDescription, itemCode, categoryID, etc.

Now if I refer to the quote

1. A Lookup field in a table displays the looked-up value. For instance, if a user opens a table datasheet and sees a column of company names, what are in the table are, in fact, a numeric CompanyID, and that ID links the table with a select statement to the company table.

Now if I base the above statement on my items table example, my item table would store the categoryType as the categoryID in the items table. To retrieve the description of the category for the item in question, I would have to select both tables in a query linking them by the categoryID fields. This to me seems to be what the statement above is saying.

Maybe an example of a basic Look-up fields setup might clear the meaning of a lookup field.

thanks
cOcO

Part of the problem as I see it, Coco, is that what you see with a lookup field is very similar to what you see (and get) using a combo box on a form to lookup the values. In both cases the values are looked up in a table. You could in fact use exactly the same table for the lookup values.

The problem comes, as I now understand it, with a lookup FIELD, from all of the overhead and baggage that German's response earlier in this thread pointed out.

In the intial design process, you have a field that stores the key of the looked up value. If you were to use the lookup wizard in designing this field then you will made the field a lookup field (and this is what is discouraged).

If you want to use lookups, do it through combo boxes on forms, not in a base table field.

Glenn

Glenn,

I think I finally understand what a look up field is .
Would I be correct in saying that if a table's field name's record source was bound to a select statement. This would be what is described as a lookup field.Therefore by saying that you must avoid this situation, you should not have any field name in a table binded to a record source?

If that is correct. Then I thankyou for your time in explaining this in further detail. ,


thanks
cOcO

I would say that you have it exactly, Coco! This thread has been a learning process for both of us, and worthwhile one at that.

Glenn

You have no idea how depressing this post is to a relative beginner who has one or more look up fields in a many of her tables. I'll swear I didn't have this much grey hair when I started this project.

I've taken 4 courses and I have six books on my desk and I don't remember ever hearing or reading any caution against look up fields in a table. I'm beginning to think there is some kind of conspiracy against teaching us Access correctly and completely.

Back to the drawing board to change all of them to combo boxes on the forms. I'll probably be back with questions and requests for recommendations on hair coloring.


Melinda

I guess I'm one of those on the fence on this subject. I DO use the lookup option when creating tables. I DO want an index on those fields. I DON'T want anybody else looking at the raw data but ME (and I know a lookup when I see it). I always go back and edit the relationship, but I've had zero problems with this.

I've been wrong before though....

Noah

Can I ask you about some "crearafication" - you are using look-up in the fields, but have an index from another table if you looking on the table itself, or you have a actual combo-box view in the fields and actual data?

I use a combo box view on the field most times. I do index a foreign key in my table in which it is foreign. I realize that the combo box is not showing me what's in the field in my table, and that I would have to use the relationship to sort/etc.

As I said, I may be completely off base here. If so, I just need some more 'convincing'.

Thank you for clarifying German! I appreciate it!

Noah

Glenn,

Thankyou once again for sticking with me on this issue, this has been a really good learning / understanding eXperience..

thankYou
cOcO

Off base or not - it is of-cause your business, and I'm not a judge here.
I think we are have a discussion - "to be, or not to be" combo-box on the table level, if it not have any functionality for user, when he don't see a tables as is, and seeing only forms and reports.



How about we are together and loudly whisper: Jeeeeerryyyy Dennisoooon, Head Wizard, where are you....

I'm with ya Tovarisch! I keep hoping Jerry, Rick, Gord, Glen, etc. will jump in with their thoughts too!

And I like having a good discussion!



Noah

I agree with Noah and German about having a discussion. This sort of discussion is one of the strengths of UA. I agree especially with German's comment that what one does in a database is ultimately one's own business. In my opinion there are few absolute rules and there may be good reason on some occasions to not follow the rules BUT before I do so I like to have a better understanding of the reasoning behind a rule. And that's where this sort of discussion is extremely helpful to me.

German's posting of the "evils of lookup fields" earlier in this thread I found especially helpful. So in this case I'm putting my money on the side of "don't have lookup fields in tables." As the thread has been developing it has come to me that the only time there might be some benefit to such a lookup is in viewing the data directly in a table's datasheet and THAT, in my book is a solid no-no. When I build a database for a client, the user NEVER sees the tables directly. And, even in my own personal databases, I seldom if ever view the data directly in a datasheet. So, in that case, I'm not really losing anything by avoiding lookup fields and, judging the the reasons cited in "the evils" I am gaining a whole lot of freedom from unecessary problems by not doing so.

(even though I don't drink beer)

Glenn