Lookup fields - saving space

[Paul H]

Say I have a table called tblPeopleInfo, one of the fields in the table is
called FavouriteFruit. The FavouriteFruit field is a lookup field and will
contain Apples, Oranges, Grapes etc..The list can be added to by users of
the database. What is the best way to construct this lookup?

Should it lookup a Table/Query or a Value List?

If it looks up to a Table/Query should the lookup table just have a single
"text" field or should it have an AutoNumber field plus the text field so
that the Lookup field in the original table only looks up an integer thus
saving space?

Thanks,

Paul

[Allen Browne]

Use a table for for the lookup values. Most databases have lots of these
little lookup tables in them.

You can use the text value as the primary key: no need for an autonumber.
Although this could use up a few extra bytes, there is really no
justification for using an artificial key value for most of these lookup
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul H" <no****@nospam.com> wrote in message
news:Yx*****************@newsfe6-win.ntli.net...

Say I have a table called tblPeopleInfo, one of the fields in the table is
called FavouriteFruit. The FavouriteFruit field is a lookup field and will
contain Apples, Oranges, Grapes etc..The list can be added to by users of
the database. What is the best way to construct this lookup?

Should it lookup a Table/Query or a Value List?

If it looks up to a Table/Query should the lookup table just have a single
"text" field or should it have an AutoNumber field plus the text field so
that the Lookup field in the original table only looks up an integer thus
saving space?

Thanks,

Paul