Alternative to Check Boxes

[Craig]

I posted a database design question earlier, but am having trouble
determining what would be the best solution.

First of all, thanks to Tim F. for your insights regarding my earlier post.
The issue I'm having is one of format/display.

I have a form with 10 checkboxes (not an option group) that the user can
select one, or multiple checkboxes that correspond to a classification of
damages (ex., damages can be classified as related to "Lost Profits",
"Royalty", "Price Erosion", "Fees/Costs", etc., or any combination of these).
So, I have a check box for each one. Now, this means that I have to have a
field in the underlying table for each one. Not good DB design, but the I
think the checkbox option is easier for the user than the alternative.

The alternative, in my mind, would be to have a separate table called
"tblDamageClassification". Then, on the input form, have a subform
referencing this table with a combobox drop-down with the 10 choices. This
would create a nice 1:M relationship. Additionally - as Tim F. inquired in
my earlier post - what if we wanted to add an eleventh option? This
alternative would solve that problem. However, there may be other issues:
What if a user selects the same damage classification two or more times? Is
there a simple validation rule that can be applied either in the underlying
table design or the combobox itself?

I hope this makes sense. Thanks for any and all help. I appreciate your
time and efforts.

 

[George Nicholson]

Take it one step further and use a Join table (tblClaimDamage):

("Claims" is an assumption on my part. Not material, but I had to call it
something)

tblClaims
-ClaimID (PK. Autonumber?)
- (presumably lots of other stuff but NO DamageClass info)

tblDamageClasses - (a static "lookup table". 10 records now, maybe 11
records tomorrow.)
-DClassID (autonumber PK)
-Description ("Lost Profits", "Royalty", etc)
-Inactive (Y/N field. Tomorrow you may want to confine users to 9 "current"
options when adding records while keeping "old" options in place for data
integrity.)

tblClaimDamage (Displayed in a subform in place of the checkboxes)
-ClaimDamageID (Autonumber PK)
-ClaimID (one-to-many to tblClaims)
-DClassID (one-to-many to tblDamageClasses)
This table will contain one record for every DClassID selected for each
ClaimID.
(i.e., what used to be 3 boxes checked per Claim now equals 3 records. Think
vertically, not horizontally.)
In addition to the Autonumber PK, create a unique index on the combination
of ClaimID & DClassID.
This index will prevent the same DClassID being selected more than once for
a specific ClaimID.

HTH,

 

[Jack MacDonald]

You can add an index (Unique values) to the underlying table that will
prevent duplicates. Create the index on the DamageClass plus the
ClaimID.

One of the things that multiple checkboxes have going for them is ease
of use -- simply click to toggle them on/off. Compare that to
selecting from a combo box, as is the standard interface for this type
of application. Click to drop the box, move the mouse to select the
item, click the item. BUT with some forethought, you can ease that
task, too. Ensure that each of the 10 items in your list starts with a
unique letter, and make use of the combo box's AutoComplete ability.

In other words -- it's easier to enter the multiple items in the
subform using the keyboard rather than the mouse if it is designed
properly.

HTH

I posted a database design question earlier, but am having trouble
determining what would be the best solution.

First of all, thanks to Tim F. for your insights regarding my earlier post.
The issue I'm having is one of format/display.

I have a form with 10 checkboxes (not an option group) that the user can
select one, or multiple checkboxes that correspond to a classification of
damages (ex., damages can be classified as related to "Lost Profits",
"Royalty", "Price Erosion", "Fees/Costs", etc., or any combination of these).
So, I have a check box for each one. Now, this means that I have to have a
field in the underlying table for each one. Not good DB design, but the I
think the checkbox option is easier for the user than the alternative.

The alternative, in my mind, would be to have a separate table called
"tblDamageClassification". Then, on the input form, have a subform
referencing this table with a combobox drop-down with the 10 choices. This
would create a nice 1:M relationship. Additionally - as Tim F. inquired in
my earlier post - what if we wanted to add an eleventh option? This
alternative would solve that problem. However, there may be other issues:
What if a user selects the same damage classification two or more times? Is
there a simple validation rule that can be applied either in the underlying
table design or the combobox itself?

I hope this makes sense. Thanks for any and all help. I appreciate your
time and efforts.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[John Vinson]

However, there may be other issues:
What if a user selects the same damage classification two or more times? Is
there a simple validation rule that can be applied either in the underlying
table design or the combobox itself?

Create a unique Index on the two fields, AccountID and
DamageClassification. In fact, these two fields could jointly
constitute the Primary Key of the table.

John W. Vinson[MVP]

 

[Craig]

Thanks to all for the advice.
--
Craig

You can add an index (Unique values) to the underlying table that will
prevent duplicates. Create the index on the DamageClass plus the
ClaimID.

One of the things that multiple checkboxes have going for them is ease
of use -- simply click to toggle them on/off. Compare that to
selecting from a combo box, as is the standard interface for this type
of application. Click to drop the box, move the mouse to select the
item, click the item. BUT with some forethought, you can ease that
task, too. Ensure that each of the 10 items in your list starts with a
unique letter, and make use of the combo box's AutoComplete ability.

In other words -- it's easier to enter the multiple items in the
subform using the keyboard rather than the mouse if it is designed
properly.

HTH






**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Tim Ferguson]

Hello again Craig

The alternative, in my mind, would be to have a separate table called
"tblDamageClassification". Then, on the input form, have a subform
referencing this table with a combobox drop-down with the 10 choices.

Yes, that's about it. So in this table you'd have (say) five rows
representing what would have been five "checked" fields before.

What if a user selects the same damage
classification two or more times? Is there a simple validation rule
that can be applied either in the underlying table design or the
combobox itself?

First of all, is there any sense in which it would be legal to have two
records for one combination of Classification and Claim? Two separate
valuations for the left-side door and the right-side door?

If the answer to that is No, then you can just create a unique index or
the PK on the two fields as John suggests.

If the answer is Maybe or Yes, then it looks as though you'd need an
extra field to tell the difference -- Valuer or ValuationDate or
whatever.

Hope that helps still


Tim F

 

[Scott Ferguson via AccessMonster.com]

How about a list box? Possibly using the multi-select property. When the
form opens, you could populate the listbox on the form from the lookup
table of possible damage classifications. This will dynamically
expand/contract the list as the records in the table change. Then, using
the records in a join table (like the one you suggested) update a [Yes/No]
column in that listbox to agree with the entries. (Sort of like check
boxes in a listbox) Use the after update event of the listbox to
add/delete records from the join table as required. This would required a
bit of coding, but would provide both the ease of use and the data
normalization you want.

Just a brainstorming thought I had.

Scott

 

[Craig]

Interesting suggestion. Thank you and everyone else for sharing their
insights.

 

[Tim Ferguson]

How about a list box?

I think we're still at the tables design stage, not the UI...


Tim F