Combo box question

[Joskin]

Hello Gurus,

I'm using Value Lists for options in my combo boxes.
For example, the Dentition combo box has a Value List of:
complete;healthy;strong;correct spacing;irregular;under developed;missing

It works fine, except that occasionally I would like to have for example,
both healthy AND strong.

In some cases on the form I would like to select 3 or 4 of the Value list
options.
Is this possible (without including 'healthy & strong' and all other
possible combinations as options in the Value list)?

Many thanks in advance,
Joskin

 

[fredg]

Hello Gurus,

I'm using Value Lists for options in my combo boxes.
For example, the Dentition combo box has a Value List of:
complete;healthy;strong;correct spacing;irregular;under developed;missing

It works fine, except that occasionally I would like to have for example,
both healthy AND strong.

In some cases on the form I would like to select 3 or 4 of the Value list
options.
Is this possible (without including 'healthy & strong' and all other
possible combinations as options in the Value list)?

Many thanks in advance,
Joskin

Not using a combo box.
If you think you will need to select more than one of the values in a
record, it would be better to have a Table/Sub Table relationship (and
Form/Sub Form) to add as many Dentition records as you wish to each
master record. Same as you would if you had a Table of Sales (Date,
CustomerName, SalesID, etc.) and a Sub Table of Sales Details
(Quantity, ItemName, Price, etc).

 

[Philip Herlihy]

Not using a combo box.
If you think you will need to select more than one of the values in a
record, it would be better to have a Table/Sub Table relationship (and
Form/Sub Form) to add as many Dentition records as you wish to each
master record. Same as you would if you had a Table of Sales (Date,
CustomerName, SalesID, etc.) and a Sub Table of Sales Details
(Quantity, ItemName, Price, etc).

Absolutely. In fact doing it any other ways violates the first Golden
Rule of databases (aka "First Normal Form") - no repeating elements.
See:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi53.htm
(after editing out the line break!).

Phil, London

 

[Joskin]

Absolutely. In fact doing it any other ways violates the first Golden
Rule of databases (aka "First Normal Form") - no repeating elements.
See:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi53.htm
(after editing out the line break!).

Phil, London

Thanks for your replies, gents.
I'm not sure that my problem is as described in "First Normal Form", Phil.

I only have one column for "Dentition" for each animal record and I just
want to include a brief description of the animal's teeth in that field,
from a dropdown list. The example I gave above enables the selection of
'healthy' or 'strong' from the Value List for the Dentition combo. But I
need to add 'healthy & strong' to the Value List to be able to have them
both as a description of the teeth of one animal, while another animal's
teeth might only be described as 'strong'.

It's combining the options from the Value List that I would like to be able
to achieve - another example:
The croup of the animal might have the options of 'correct length; slightly
short; short; slightly steep; steep; flat; well moulded' - all can easily be
selected individually. But the croup might be 'slightly short & slightly
steep' or several other possible combinations of the options.
I'm trying to find a way of entering, say 2 or 3 options from the list into
the one field, without having to fore-guess the combinations and add them
all to the Value List.

Regards,
Joskin, Dartmoor

 

[Philip Herlihy]

Thanks for your replies, gents.
I'm not sure that my problem is as described in "First Normal Form", Phil.

I only have one column for "Dentition" for each animal record and I just
want to include a brief description of the animal's teeth in that field,
from a dropdown list. The example I gave above enables the selection of
'healthy' or 'strong' from the Value List for the Dentition combo. But I
need to add 'healthy & strong' to the Value List to be able to have them
both as a description of the teeth of one animal, while another animal's
teeth might only be described as 'strong'.

It's combining the options from the Value List that I would like to be able
to achieve - another example:
The croup of the animal might have the options of 'correct length; slightly
short; short; slightly steep; steep; flat; well moulded' - all can easily be
selected individually. But the croup might be 'slightly short & slightly
steep' or several other possible combinations of the options.
I'm trying to find a way of entering, say 2 or 3 options from the list into
the one field, without having to fore-guess the combinations and add them
all to the Value List.

Regards,
Joskin, Dartmoor

Well, I've read your posts carefully again, and I still think that
you're heading the wrong way, and that FNF is relevant. You have to
develop an "eye" to recognise it.

I'm guessing you're doing some sort of study, and you'll want to analyse
some other observable against these values. How you store them will
determine how easy that analysis is.

Say you have two animals, one with strong teeth and one with strong and
healthy teeth. If you simply enter "strong, healthy" in a text field,
you're violating FNF, but you could run string comparisions to see if
the string "healthy" is contained within the stored value. This can
cause unexpected problems - some residents of the English county of
Essex don't get as much email as they should, for example.
Alternatively, you could create codes, where 52 means strong and healthy
and 76 means healthy, but again, if you want to pick out all the animals
with healthy teeth, you're going to have to use an OR condition in your
queries.

Relational databases have idioms, and the idiom for this is to have a
record for each property, so if an animal had strong, healthy and
protruding teeth there would be three records related to the animal's
master record. If you want ease of data entry (and who doesn't) you'd
want a form which allowed you to select (or enter) the details of an
animal, and a subform contained within the main form which allowed you
to add individual characteristics from your list. This could take the
form of selecting the property from a combo box (or a list box if there
are few enough of them) and clicking a button labelled "Add".

I think it likely that you'll have a lot of head-scratching to do when
you come to do your analysis if you organise things differently.

Phil

 

[Joskin]

Well, I've read your posts carefully again, and I still think that you're
heading the wrong way, and that FNF is relevant. You have to develop an
"eye" to recognise it.

I'm guessing you're doing some sort of study, and you'll want to analyse
some other observable against these values. How you store them will
determine how easy that analysis is.

Say you have two animals, one with strong teeth and one with strong and
healthy teeth. If you simply enter "strong, healthy" in a text field,
you're violating FNF, but you could run string comparisions to see if the
string "healthy" is contained within the stored value. This can cause
unexpected problems - some residents of the English county of Essex don't
get as much email as they should, for example. Alternatively, you could
create codes, where 52 means strong and healthy and 76 means healthy, but
again, if you want to pick out all the animals with healthy teeth, you're
going to have to use an OR condition in your queries.

Relational databases have idioms, and the idiom for this is to have a
record for each property, so if an animal had strong, healthy and
protruding teeth there would be three records related to the animal's
master record. If you want ease of data entry (and who doesn't) you'd
want a form which allowed you to select (or enter) the details of an
animal, and a subform contained within the main form which allowed you to
add individual characteristics from your list. This could take the form
of selecting the property from a combo box (or a list box if there are few
enough of them) and clicking a button labelled "Add".

I think it likely that you'll have a lot of head-scratching to do when you
come to do your analysis if you organise things differently.

Phil

Phil,
Many thanks for taking the trouble to explain that.
The data comes from animal surveys, where currently the whole survey is
printed & read (by humans) in consideration for animal mating.
The future (computerised) analysis of the surveys is something I hadn't
considered at all, but thanks to your explanation, I can now see the
problems that I would be stacking up.

Now it's back to the drawing board for me :-(

Thanks once again,
Joskin

 

[AccessVandal via AccessMonster.com]

You can take a look at some data models from Barry Williams, try the Animal
Shelters or Vets Practice models

http://www.databaseanswers.org/data_models/index.htm

Some users have tendency to misunderstand things on FNF.

 

[Joskin]

You can take a look at some data models from Barry Williams, try the
Animal
Shelters or Vets Practice models

http://www.databaseanswers.org/data_models/index.htm

Some users have tendency to misunderstand things on FNF.

Misunderstand it ??
I'd never heard of FNF until this post

I created our database to hold the separate, paper based records of over
16,000 animals, their owners, breeders, surveyors & surveys.
I've been learning from examples, templates, & copious use of the F1 key,
but, just when I thought I knew what I was doing, I've been made aware of
even more giant holes in my knowledge & understanding.

Such are the joys of coming to terms with new concepts.

Thank you all for your help,
Joskin

 

[Philip Herlihy]

Misunderstand it ??
I'd never heard of FNF until this post

I created our database to hold the separate, paper based records of over
16,000 animals, their owners, breeders, surveyors & surveys.
I've been learning from examples, templates, & copious use of the F1 key,
but, just when I thought I knew what I was doing, I've been made aware of
even more giant holes in my knowledge & understanding.

Such are the joys of coming to terms with new concepts.

Thank you all for your help,
Joskin

AccessVandal's comment seems aimed more at me than you, although he
hasn't said why. Even the MVPs here occasionally correct each other,
and I'm certainly happy to be challenged, and always pleased to shed a
misconception. Still seems plain to me so far, though.

Don't get bogged down in the technicalities of the various normal forms
(the guru Chris Date says there are six). For most of us, a few key
questions will do:

Do I need a further table here?
Should these things be in separate records?
Is this a one-to-one, one-to-many, or many-to-many relationship?
Is every fact in exactly one location?
Does every location hold no more than one fact?

You get to "see" it (possibly imperfectly) quite quickly. Nevertheless,
getting your table design right is (probably literally) more than half
the battle, and if you focus on what you need to store, the other
aspects will fall out much more easily. Store it badly, and you'll
curse (or be cursed) in time for the complexity you'll have introduced.

Now that I understand your situation better, it seems to me that a
simpler approach will do. These dental terms could be seen simply as
"attributes" of an animal and could be Yes/No fields in a table. The
fields could be tacked on to the table which holds animal details.
Then, in your data-entry form, you could have a check-box for each field
(possibly hived off into a separate tab on your form, if that's more
convenient).

However, if these attributes are reassessed in multiple surveys, you'd
need to keep them in a separate table of Surveys. This, apart from the
dental characteristics you're recording, would have a reference (Foreign
Key) to the entry in the Animals table, and probably another to an entry
in a Surveyors table, together with a date. "One (animal) to Many
(surveys)" invariably means two tables related by a key field rather
than one.

You might be interested to note that the List Box (the combo box) does
allow you to select multiple values if you set the appropriate property
on the control. However, whereas a simple control can be "bound" to a
field in your table, you'd now have to write code to loop through the
"collection" of values and write them into the correct fields in the
current record - much more complex. See:
http://bytes.com/topic/access/answers/553607-multiple-selection-list-box-combo-box

Note that if, half-way through your data collection, you start recording
another property ("impacted", say) then you'd need to add a new table
field and a new checkbox, but you'd also need to work out how to
distinguish animals not rated on this condition from those rated but not
qualifying.

There's nothing wrong in keeping records simply when your needs are
simple, but if you're thoughtful about it you could be building a
resource which can be used in increasingly sophisticated ways in the
future - if your data is "clean" it can always be rearranged.

Best wishes,

Phil

 

[Beetle]

I would caution you against using a collection of check boxes to
store these attributes. The problem with that approach is that as
soon as you have a situation where you need to add another attribute
(which will inevitably happen), you not only need to add another
yes/no field to your table, you also have to subsequently re-design
every query, form and report that is based on that table.

These attributes should be stored in a separate table that is related
1:m to the main Animal table. That way, any given record in the
Animal table can have as many, or as few, Dentition attributes
as you need. This table would actually be a juntion table to handle
what is essentially a many-to-many relationship between Animals
and Dentition attributes (a given Animal can have one or many
Dentition attributes, and any given Dentition attribute can apply to
one or many Animals). So the junction table (let's call it
tblAnimalDentitions)
would be 1:m with tblAnimals and 1:m with tblDentitionDescriptions
(I would put the Dentition descriptions in a table - not a value list).
That way, if you need to add another Dentition attribute at some point in
the future, you just add another *record* (not a field) to
tblDentitionDescriptions. No need to go back and re-design anything.

Likewise for your Croup attributes, and any others you may have.

_________

Sean Bailey

 

[AccessVandal via AccessMonster.com]

You can use Phil method with Option Groups with a single column/field in the
table but you need to create some complicated codes to deal with input values
and as well as complicated Option Groups.

Or

You can use Beetle’s suggestion than use Duane Hookom’s sample to concatenate
the values.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Or look for Allen’s Browne’s function at

http://allenbrowne.com/func-concat.html

Rather than using Value List, I would suggest you create a lookup table for
this too and who knows, you might need more input than you had suggested.

Note that “&†is a reserved character and if you encounter any problem with
these, check Doug Steel’s sample here.

http://www.accessmvp.com/djsteele/SmartAccess.html

Look for May 2004: Here's a shortcut, may I quote you? and what have I done?
(176 KB)
http://www.accessmvp.com/djsteele/Access/AA200405.zip

 

[Philip Herlihy]

I would caution you against using a collection of check boxes to
store these attributes. The problem with that approach is that as
soon as you have a situation where you need to add another attribute
(which will inevitably happen), you not only need to add another
yes/no field to your table, you also have to subsequently re-design
every query, form and report that is based on that table.

These attributes should be stored in a separate table that is related
1:m to the main Animal table. That way, any given record in the
Animal table can have as many, or as few, Dentition attributes
as you need. This table would actually be a juntion table to handle
what is essentially a many-to-many relationship between Animals
and Dentition attributes (a given Animal can have one or many
Dentition attributes, and any given Dentition attribute can apply to
one or many Animals). So the junction table (let's call it
tblAnimalDentitions)
would be 1:m with tblAnimals and 1:m with tblDentitionDescriptions
(I would put the Dentition descriptions in a table - not a value list).
That way, if you need to add another Dentition attribute at some point in
the future, you just add another *record* (not a field) to
tblDentitionDescriptions. No need to go back and re-design anything.

Likewise for your Croup attributes, and any others you may have.

_________

Sean Bailey

Which brings us back to the first suggestion in this thread!
(Nicely set out in an article I spotted just this morning:
http://allenbrowne.com/casu-23.html )

Yes, this is certainly better - no question. However, sometimes there's
a pressing need to get on with data collection and the user/developer is
in danger or grinding to a halt if the immediate benefits of the better
(but less obvious) approach aren't readily evident. Better then to help
them do something adequate while successfully steering them away from
something barbarous. Just my 2p worth.

Phil