multiple check boxes for one field

[Guest]

Hello and first Thanks for all those who provide sooo much help here!

Ok, with Access 2002 I am trying to have a Form with many check boxes, or
radio buttons, that will all enter the selections into one field in one
Table. As an example, say the Form is Customer Orders for a pizza. And each
check box is a different topping. And if multiple check boxes (6-8) were
selected each would enter Sausage, Extra Cheese, etc. into one field -
Toppings. Is this possible?

Also there is only one table, say 'Customer Orders', would it be better to
have a second Table for just 'Toppings'? Been reading that that is better
structurally... total access newbie here Thanks!

 

[Jeff Boyce]

Jason

One of the tenats of good database design is "one fact, one field". It
sounds like you are proposing to add multiple facts ("sausage, extra cheese,
....") into one field. This is not a good idea.

Not sure what you've been reading...so take this will a grain of salt
(JOPO-just one person's opinion):

There is no hard and fast rule about how many tables you need for
"structural" reasons. In my opinion, the reason for normalizing table
structure in Access is so that I can make fuller use of Access'
relationally-oriented features and functions.

Note that no mention of forms has appeared -- unless you get your data
well-normalized, you (and Access) will be struggling to make the forms
work.

If "normalizing" is not something you've run across, try posting to the
tablesdbdesign newsgroup for more suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I actually just did this in a new database application for our Security Dept.
It was for things like tattoos, piercings and facial hair. I have checkboxes
for each category. Under each checkbox, I have a multi-selection-enabled
listbox for WHERE these identifying characteristics are located. For example,
for tattoos: Right Arm, Left Arm, Right Leg, Left Leg, Neck, etc. When the
user clicks the checkbox for tattoos, he can then select as many "locations"
for tattoos as he wants via the listbox.

Then, in the BEFORE UPDATE event of the form, I spin through the listbox's
SELECTED items and build a semi-colon-delimited string, like this: "Right
Arm;Neck;Left Ankle;" That data then gets placed into a SINGLE field in the
table. (Although Jeff Boyce is correct about database design, in this case it
was preferable to do it this way rather than have about 20 different Location
columns in a separate table.)

The only issue I had was when the user stepped through the already-created
records. I had to "decompile" the concatenated string and RE-select
(highlight) the correct Locations in the listbox. (The listbox is UNBOUND you
see, so has to be processed manually.) Definitely some real "fun" code to
write (I love a puzzle like that - heh)

But the concept would work just as well using checkboxes, although the
listbox construct would IMO be cleaner to implement. Hope this helps.

 

[Guest]

Will an Option Group do it for you?

 

[Guest]

The problem you may run into, Dennis, is there is a 255 character limit per
field. If security tries to put in someone from Miami Ink, they will blow
your database

You could use something akin to an order processing concept as should the OP.

That is. A customer make make one to many orders. An order may contain one
or more items. An item may have 0 to many attributes.

That is, Joe orders two pizza's. One is a Large Thick Crust with pineapple
and anchovies. The other is a Medium Thin Crust with extra cheese, and 1/2
pepperoni, mushrooms, and onions and 1/2 with sausage and mushrooms.

So you see you need to be able to break it down into at least 3 levels.

Customer Table - Everyone who has or is ordering pizzas
CustID
etc.

Order Table - Header Info for Order
OrderID
Special Instructions
Directions

OrderItems - Items Ordered from Inventory
OrderID
ItemID
Qty

OrderAttr - Specific Items added on
OrderId
ItemId
ItemSide - Left side/Right side/Whole
AttrID - ie toppings from topping table

Note, I have done this a long time ago on a different platform, so I know
you will get requests for extra this, light that/ stuff on one half.

In addition, you need an inventory table that has the types of pizzas and
special types with toppings already on them, etc. but you also have to deal
with "I want a special, but no sauce", so you have to deal with that as well
as pricing. That can be the hard part. Like a 3 topping large is 9.95 or a
medium plain cheeze is 7.95, first topping is free, then .50 per topping, etc.

 

[Guest]

Oh absolutely! However, he COULD use a number as his bound field (display
length = 0), and do it that way. But yeah, in his case, it's probably better
NOT to do it the way I did. In my specific case, the approach I described
proved to be perfect. And the coding was fun!

 

[Bob Quintal]

And if, instead of this design, you had normalized the structure
and had a child table for distinguishing features, you would not
have had to write this kludgy code, and you would be able to
query the database easily for questions like show me all the
people who have a tattoo of a snake on their left forearm.

Proper design says use a separate row in the child table for
each of the features.


With your bad design, it's a hit or miss situation if the person
has 3 other tattoos. so please do not reccomend it to neophyte
database designers.

Q

I actually just did this in a new database application for our
Security Dept. It was for things like tattoos, piercings and
facial hair. I have checkboxes for each category. Under each
checkbox, I have a multi-selection-enabled listbox for WHERE
these identifying characteristics are located. For example,
for tattoos: Right Arm, Left Arm, Right Leg, Left Leg, Neck,
etc. When the user clicks the checkbox for tattoos, he can
then select as many "locations" for tattoos as he wants via
the listbox.

Then, in the BEFORE UPDATE event of the form, I spin through
the listbox's SELECTED items and build a semi-colon-delimited
string, like this: "Right Arm;Neck;Left Ankle;" That data then
gets placed into a SINGLE field in the table. (Although Jeff
Boyce is correct about database design, in this case it was
preferable to do it this way rather than have about 20
different Location columns in a separate table.)

The only issue I had was when the user stepped through the
already-created records. I had to "decompile" the concatenated
string and RE-select (highlight) the correct Locations in the
listbox. (The listbox is UNBOUND you see, so has to be
processed manually.) Definitely some real "fun" code to write
(I love a puzzle like that - heh)

But the concept would work just as well using checkboxes,
although the listbox construct would IMO be cleaner to
implement. Hope this helps.

 

[Guest]

Yup. I understand. But their design criteria exclude that type of query (for
some odd reason). Personally, I waffled a bit when deciding how to implement
the row source for the drop-downs. In THIS CASE (admittedly unusual), it
seemed that (gag) coding the dropdowns as a "value list" was what they wanted
to go with.

*sigh*

Dennis

 

[Bob Quintal]

Yup. I understand. But their design criteria exclude that type
of query (for some odd reason). Personally, I waffled a bit
when deciding how to implement the row source for the
drop-downs. In THIS CASE (admittedly unusual), it seemed that
(gag) coding the dropdowns as a "value list" was what they
wanted to go with.

*sigh*

Dennis

I've found that too many design criteria are compiled by people
who know just enough to be dangerous. When I'm asked to do
something wrong, I usually ask for a waiver from or an amendment
to the specification 99.9% of the time, they say "we found this
in another document we copied from" and I explain that maybe the
details were slightly different, or that they discovered after
using it that the db had to be fixed, etc.

Most of the time I get a thank you. Not necessarily the job,
sigh.


Q

And if, instead of this design, you had normalized the
structure and had a child table for distinguishing features,
you would not have had to write this kludgy code, and you
would be able to query the database easily for questions like
show me all the people who have a tattoo of a snake on their
left forearm.

Proper design says use a separate row in the child table for
each of the features.


With your bad design, it's a hit or miss situation if the
person has 3 other tattoos. so please do not reccomend it to
neophyte database designers.

Q