Lookup Tables

[Guest]

Hello,

Basic design question.

In the case where I wish to make a table which is used as a lookup source in
my form for the marital status of each individual is there anything wrong
with only having one field in the table (option 2) and using the status as
the primary key instead of using an autonumber field? (I am also wanting to
plan for the future and upsizing eventually)

Option 1
MaritalStatusID Autonumber - Primary Key
MaritalStatus Text

Option 2
MaritalStatus Text - Primary Key

Thank you

Daniel P

 

[Douglas J. Steele]

Realistically, no, there's nothing wrong with it. The only potential issue
is that if Marital Status is more than 4 characters in length, the index for
the table will be slightly larger. However, given how small the table will
be, that shouldn't be an issue.

 

[Guest]

Hi Daniel

My personal view (may be wrong) is that in a database the less you have the
better it will be (for many reasons). Anyway - as there are only ever going
to be a limited numer of options to martial status it seems a waste to have a
full table just for these options.

You may be better using a combo or list box on a form to select the status.

Format the field in the table to text. Provideing the your form is
correctley baed on the table/query you can set the combo's control source as
the table field (select it from the drop down)

On your form set the combo's source type as Value List

Set the combo's row source as
"Married";"Sinlge";"Divorced";"Window";"Widower" ... etc

Hope this helps

 

[Guest]

Although this particular instance is quite defined (only so many options
possible) what about those case where I want the user to be able to add
choice to existing lists? In theses case a lookup table is well justified or
do you have another work around?

 

[Guest]

HI Daniel

I can’t really think when you would want to (or be able to) add to a list of
Marital Statuses / Statui / Statei / Statu / Status’ (LoL what is the English
plural of status – oh well, you get it)

Anyway there will never be many instances so I would still use a combo in
this case as (if needed) a user can enter something not in this combo already
(you can not do this with a list box)

If you “really†want to use a look up table then don’t use the lookup
directly from one table to another – use a lookup from a form (much better
for many reasons)

Create the lookup table (something like)
tblMatitalStat
Primary Field format as text and use Married, Divorced, Single, etc.
I would call the primary field “Statusâ€
Note this is a 1 field table.

On your form create a combo and use

Row Source Type = Table/Query

Row Source = SELECT [tblMatitalStat].[Status] FROM [tblMatitalStat] ORDER BY
[Status];

Bound Column = 1

As I said this seems to be over the top but you know your DB and I don’t so
you will know best in this case.

Good luck