Which is better?

[Access_Novice]

Suppose I have a table called "Contacts" with a field called Gender. In order to populate the Gender field in Contacts, which is the better solution and why:






A. Create another table called "Gender." The Gender table has autoNumbers and then the gender, e.g. 1 = Male, 2 = Female, 3 = Not specified. In the Gender field of Contacts, enter the correct gender ID number for each person. Then after that, in Design View in the Gender field data type, use the Lookup Wizard to create a drop down menu, where the values in the drop down are imported from the Gender table.

B. Do not create a separate Gender table. In the Gender field in Design View, use the Lookup Wizard to create a drop down menu, where I type in the possible gender values.

[RuralGuy]

Start by reading this link: http://access.mvps.org/access/lookupfields.htm

[June7]

I NEVER set lookups in tables if there is alias involved for the reasons enumerated in the link provided by RG, and rarely bother to setup combo/list box in table even if alias is not involved.

Whether or not to use a table as source for the 3 gender options is a matter of choice, and saving an ID or the descriptor is another choice. I vote for M, F, N and no table.

[KathyL]

For something as small as having only 3 options, having a separate table is rather silly. Use a dropdown. I’d put the dropdown on a form, such as a List Box, where you’d just type in the 3 values you’d want to choose from. As a 30 year application designer, I rarely put edits and requirements into the table, I keep tables basic, and put all my criteria and editing in a form, often using Visual Basic events behind the form.

[Dal Jeanis]

C. Create another table called "Gender." The Gender table has three records for the gender, "Male", "Female", "Not specified". In Design View in the Gender field, use a listbox with your Gender Table as its record source, restricted to the list.

[ItsMe]

C. Create another table called "Gender." The Gender table has three records for the gender, "Male", "Female", "Not specified". In Design View in the Gender field, use a listbox with your Gender Table as its record source, restricted to the list.

I agree.

edit: except I prefer not to use lookup fields.

Having it in a table will help to remind you what Key = what gender. If a developer comes in behind you to expand your app, they won't have to search for a Key value. I use a naming convention for my tables such as tblCustomers, where tbl represents a tbl. If the table is something that lists out states or gender, etc. I use lst to help distinguish what the table is storing. lstGender, lstStates

Just a suggestion.

[June7]

My preference would be to not have a key for such a small number of brief descriptors. Just save the actual text value - no alias, no table joining, no translation between key and descriptor.

[ItsMe]

It is a good thing the OP asked. At least now they have a concrete answer!

[Access_Novice]

Start by reading this link: http://access.mvps.org/access/lookupfields.htm

Thank you everybody. This makes sense. So I would not use a Lookup to mask the underlying ID number. Instead, I think I will just have a Lookup where I type in the values that can appear. That way nothing is masked. I think another poster mentioned this possibility.

[Access_Novice]

For something as small as having only 3 options, having a separate table is rather silly. Use a dropdown. I’d put the dropdown on a form, such as a List Box, where you’d just type in the 3 values you’d want to choose from. As a 30 year application designer, I rarely put edits and requirements into the table, I keep tables basic, and put all my criteria and editing in a form, often using Visual Basic events behind the form.

I think I will go with this. Thank you KathyL.