setting up tables

I have a couple of questions, both relating to the same
database.

1. The database I'm working on includes tblInternetSpecs
with a field strFormat. The user can choose 'web
page', 'email', 'banner', or 'other'. If they choose
other, they need to provide a description. How should I
set this up? At first I was going to do a lookup, but
then I'm not sure how to retain the 'other description.'
Then I thought I could simply set the field up as a text
field, and then on the form have an option group where I
can pick up the value? I actually have several fields in
this table that act this same way, so I need to be sure
I'm doing it correctly.

2. In the same table, there is a strLink field where the
user can provide more than one value. I currently have it
set up as a 'Link ID' field in the parent table, a child
table with Link ID(PK) and Piece ID (FK from parent
table), and LinkName field. Is this the best way to do
this?

Thanks in advance.

Christy

You didn't mention how the user was presented the choices for the strFormat
field -- are users working directly in the table? Is the field designated a
(?!shudder?!) lookup field? Or are you working on a form that is bound to
the table, and with a combo box that lists the choices? If the latter, what
is the source of the rows in the combo box?

If you need to preserve an "other" + "description", you'll need another
field to hold the description.

As for using an option group on your form, be aware that option groups rely
on the numeric value of the option chosen, and that these don't have any
necessary connection to the value of the caption (e.g., "email", "web page",
....). This is true for any option group. Are you using "lookup tables"?
That is, tables that have lists of choices, and whose ID values you are
inserting in your main table via combo boxes on the form? If you haven't
looked into what combo boxes (and list boxes) can do for you, read up on
them in Access HELP.

Having more than one value in a field is possible in Access, but not
desirable. Also, it isn't a well-normalized design to do so. Having
(potentially) multiple values in a single field requires you to create all
the parsing/handling routines to break the strung-together string apart, to
search for a value somewhere in the string, etc. Access is a relational
database, but it's strengths won't work if you don't design your data with
normalization in mind. Check Access HELP on normalization, too.

--
Good luck

Jeff Boyce
Access MVP

Let me try to explain better. I have 2 different
situations.
First, I need a way using a form to retain info in a
field 'format.' Possible values for this field would
be 'web page' 'email'... or whatever the user enters. I
don't necessarily need to retain the word 'other.' What's
the best way to set up the table and/or form?

Second, I wasn't going to retain multiple values in one
field. I want a new 'record' for each value. So I have
it set up as:

tblInternetSpecs tblLinks
SpecsID (PK) LinkID (PK)
LinkID SpecsID (FK)
etc.. LinkName

Is this correct?

Thanks again.

-----Original Message-----
Christy

You didn't mention how the user was presented the choices
for the strFormat
field -- are users working directly in the table? Is the
field designated a
(?!shudder?!) lookup field? Or are you working on a form
that is bound to
the table, and with a combo box that lists the choices?
If the latter, what
is the source of the rows in the combo box?

If you need to preserve an "other" + "description",
you'll need another
field to hold the description.

As for using an option group on your form, be aware that
option groups rely
on the numeric value of the option chosen, and that these
don't have any
necessary connection to the value of the caption
(e.g., "email", "web page",
....). This is true for any option group. Are you
using "lookup tables"?
That is, tables that have lists of choices, and whose ID
values you are
inserting in your main table via combo boxes on the
form? If you haven't
looked into what combo boxes (and list boxes) can do for
you, read up on
them in Access HELP.

Having more than one value in a field is possible in
Access, but not
desirable. Also, it isn't a well-normalized design to do
so. Having
(potentially) multiple values in a single field requires
you to create all
the parsing/handling routines to break the strung-
together string apart, to
search for a value somewhere in the string, etc. Access
is a relational
database, but it's strengths won't work if you don't
design your data with
normalization in mind. Check Access HELP on
normalization, too.

--
Good luck

Jeff Boyce
Access MVP

.

Christy

If you use a text box, it isn't very easy to constrain how a user spells
what s/he enters. If you need/want to build queries/reports based on what's
contained in that field, it might be easier for you to force a choice.
Otherwise, you'd have to check for "web page" and "webpage" and "web-page"
and "web pgae" and ...

To limit choices to a list, you can create a small table that contains valid
values. In your form, you would use a combo box, bound to this small
(lookup) table to list valid choices. In the properties of this combo box,
you would set LimitToList to Yes, and create a procedure for the NotInList
event. This procedure would be triggered when someone entered a value that
wasn't in the list (i.e., small table). The procedure would give you (and
the user) the means to add a new value to the table/list, so it would be
available the next time the combo box is used.

As for your table structure, I don't understand why you have cross-linked
the keys in the two tables. Maybe instead of describing how you are trying
to do something, you could explain what you want to accomplish. I don't
"get it" when you say

I want a new 'record' for each value.

--
More info, please ...

Jeff Boyce
Access MVP