Is it considered bad design to use Look Ups for Table Fields?

[c.d.power]

In several of my tables, I've used the Lookup function for some of the fields. The only thing I ever use is the Value List function. I manually type in the values I deem necessary and I give users the option to execute Value List edits if necessary. My thought process is they will know better than me which values are meaningful to them in most cases.



I ask the question because I was reading an article about database normalization and it seemed that the suggestion was I should create a tables for these fields rather than use Lookups. Am I understanding this correctly?

Thank you in advance for any advice on this.

[June7]

Sounds like what you read was really advising against multi-value field.

As for lookups, they don't have to involve multi-value field http://access.mvps.org/Access/lookupfields.htm - build combo or list box on form.

Most experienced developers advise against both multi-value fields and lookup fields in table. I agree.

[Micron]

I second the motion. They are a crutch for two groups: those that don't know any better and those that don't care.

[c.d.power]

Just to clarify, using the Combo Box / Value List as the Row Source is a bad idea? I've attached an example of what I've done.

And if so, the appropriate alternative is to have a separate table that contains the Combo Box values I want for my DB? Is it good practice to create just one table that contains all Combo Box values for all tables and then to have a form where a user can edit those values as necessary?

[June7]

This is not so bad because there is no alias involved. The value you see is the actual value saved. Also, not establishing a relationship to a 'lookup' table. Even if I did build lookup in table I would not use the Lookup wizard, I would not select Lookup field type. I avoid all the wizards because they do things I don't like.

Have a table if you want to allow end user more flexibility in what these values are.

Maybe this will interest you http://www.mendipdatasystems.co.uk/c...ies/4594455053

[Micron]

If that question is applied to a form combo, then not so much. An addition requires code or a redesign of the form control.
If you're asking about a table, it matters not what the row source is when it comes to the reasons for not having it at all. Don't know what you read; this is what I usually offer on the subject

http://access.mvps.org/access/lookupfields.htm

[c.d.power]

This helps. Thank you for the quick replies! Marking thread as solved now.

[Micron]

The answers seem to have overlooked a rather important point - that image represents a combo in a table. Editing directly in tables is not recommended, so if you follow that design principle, whether or not your pic represents something that is not so bad or downright vile is kind of moot.

[June7]

Right, users should not work directly with table/query, only form/report. All setting lookup in table really accomplishes is when form is created, dragging fields to form will allow Access to create the appropriate control with those settings for you. Which is actually a nuisance when building a report because comboboxes make no sense on report. If you later change the settings in table, they may or may not carry over to already created forms (usually get prompted to verify this).

My practice is to do as little formatting in tables as possible. Do design on forms and reports.

[c.d.power]

Right, users should not work directly with table/query, only form/report. All setting lookup in table really accomplishes is when form is created, dragging fields to form will allow Access to create the appropriate control with those settings for you. Which is actually a nuisance when building a report because comboboxes make no sense on report. If you later change the settings in table, they may or may not carry over to already created forms (usually get prompted to verify this).

My practice is to do as little formatting in tables as possible. Do design on forms and reports.

Thanks for clarifying. My DB is not designed so that users will work directly with tables whatsoever. I do have Split-forms which include the input fields and an editable version of the table on the bottom half of the form. However, the idea is that they would never go anywhere near the table. As for the combo boxes, I have set them up in the table so that, like you said, when I add the field to the form, the combo box is already existing.

[June7]

How often do you need same combobox on multiple forms? If I really need another of same combobox, I would copy/paste from existing form.

I still vote on the side of not building in table. Tiny bit of convenience is not worth the headaches they cause.