design question

[ionic-fire via AccessMonster.com]

I am developing an acess 2003 database on winXP pro and have hit what appears
to be a stonewall.

Right now, I have the user check a check box to select an item and uncheck to
de-select the item. These check boxes are hard-coded, and are bound to an
integer field in a table (where it stores -1 for true or checked, and 0 for
false or not checked).

Later on in my search form, I have the user check a check box for each item
to search for, and the underlying code does a check for check box checked (if
boolChecked then...). If checked, the code adds another condition to my
whereclause to filter records having a -1 in the particular field in my table.


Problem is, the users would like to be able to dynamically add in new items
(instead of having me hard-code new controls, add table fields, and code
another whereclause condition) and be able to search for them as well. This
would entail having code to add in a field to a table, and add checkbox
controls to several forms, plus adding in another section of code to add this
new item to a whereclause for searching, not to mention my report later on
needing some addition.

I am having trouble implementing this using my current scheme of check boxes
and storing -1 or 0 to a table. Does this sound like it is possible to do? I
am just looking for some direction, thank you.

 

[Allen Browne]

Storing a check box in your table for each possible type of entry is not the
right way to design a relational database. You have already described part
of the reason for that: it's a maintenance nightmare.

The relational design is to create a related table where you enter a
*record* for each applicable value. Details and examples in:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

 

[mscertified]

Create a table called tblSearchItems to hold things the user wants to search
for.
This table can be updated by the user (via simple datasheet).

Then have a multi-select listbox where the user can pick from this table.
Examine the listbox selections to build your WHERE clause.
Get rid of all your checkboxes.

-Dorian

 

[John W. Vinson]

I am developing an acess 2003 database on winXP pro and have hit what appears
to be a stonewall.

Well... you built it, you should probably be the one to tear it down. <g>

The problem is that you're "committing spreadsheet" - storing data in
fieldnames. Whenever you need to add a new field and a new check box whenever
the users' interests change, you need to step back and look at your table
design.

You don't say what kind of Entity (real-life person, thing or event) the table
represents, nor what the fields might be - but I suspect you'll do better to
normalize. Rather than one table with many fields, consider using THREE
tables:

MainTable
EntityID Primary Key
<other information about the entity>

Attributes
AttributeID <Primary Key>
Attribute <what you're now using as the fieldname of your yes/no fields?

Values
EntityID <link to MainTable>
AttributeID <link to Attributes>
AttributeValue <Integer>


John W. Vinson [MVP]

 

[ionic-fire via AccessMonster.com]

Check boxes are all gone. I must thank you for your suggestion. It does
indeed work much smoother and maintenance is not a headache anymore. Thank
you.

Create a table called tblSearchItems to hold things the user wants to search
for.
This table can be updated by the user (via simple datasheet).

Then have a multi-select listbox where the user can pick from this table.
Examine the listbox selections to build your WHERE clause.
Get rid of all your checkboxes.

-Dorian

I am developing an acess 2003 database on winXP pro and have hit what appears
to be a stonewall.

[quoted text clipped - 20 lines]

and storing -1 or 0 to a table. Does this sound like it is possible to do? I
am just looking for some direction, thank you.