Combo Box Without Foreign Key?

[dbrooks]

Anytime in the past when I had a option in the database about sex of a person, (like Female or Male), I would create a table called "SEX" then put male or female in as a foreign key to the main table.

Is this better then just using the option of a value list and a text box within the same table? I just noticed that if I make this column a combo box intead of a text box (but format it as text) I can use a value list. In the value list I can put Male; Female. Then I will have my two choices. Why would I not want to use this?

If anyone knows for certain please let me know. Thanks very much. -db

 

[Pat Hartman]

Good question. I don't use value lists for a several reasons:

1. When they are defined in a table, they affect all queries and if you use a numeric identifier and a text description, your queries ALWAYS show the text description. On the surface this looks like a plus. However, users should never be presented with queries, they should only work with forms. So the only time queries are used is for behind the scenes work and the de-coding gets in the way behind the scenes because it messes up joins when you use nested queries.

2. When the combos are defined in the table definition, any forms generated from the table while using the wizards result in combo boxes for the fields defined that way in the table. This is good.

3. However, combo boxes are also generated by the report wizards. This is bad.

4. If you change the value list in the table, that does NOT propagate to any value lists already existing on forms. You still need to change those yourself.

5. If you don't define the value lists in the table, you must recreate them any time that you need a combo for a form.

6. If you use value lists, you can't give your user the ability to update the list.

All in all I find it much better in the long run to use tables. The first thing that I add to most databases is my canned lookup tables table and forms and reports.

I've attached a database that contains the common lookup table setup that I use. The db also includes minimal user-defined security. It is A97 so you can all make use of it.

 

[jfgambit]

Pat:

I just wanted to say that as usual, you have proven yourself an access guru.

I'm always impressed by your answers, and wanted to get the extra post number...he he

BTW...do you ever sleep?

 

[dbrooks]

Pat,
Thanks for your response. You have always been a great help with all of my questions. I think you are right I would want to go with Tables, however if I just have two possible choices like "Male";"Female" this would probably work with a value list, correct? I am going to continue using tables, unless I get your opinion on a situation like "Male";"Female".

Thanks very much.

-db

 

[Pat Hartman]

Since I use a single code table, I use it for EVERYTHING. I would use it for sex code also.