Table design - field limits

[dave]

I am attempting to create a checkbox form that contains
205 checkboxes. What I need to do is set up the form so
that when the checkbox is checked, the box's label is
stored in the underlying table for later merge into Word.
The problem is that my design calls for two fields for
each check box. One field binds the "checkbox" and the
other field binds a hidden text box that stores the
checkbox's label when the box is checked. Since I have
205 checkboxes and 205 attached invisible textboxes for
storing the checkbox labels, I am unable to create a table
because Access limits its table and query fields to 255.
Does someone out there have a workaround for this
problem? I have struggling with this for over a year and
although I have consulted "experts" from around the globe,
none have been able to come up with a workable solution.
To compound the problem, I need to have a query that links
two tables (from two different forms) and that makes the
grand total of fields to over 550!

If anyone has any suggestions I would greatly appreciate
your help. I am recreating a form and table that was
originally done in FileMaker Pro on a Macintosh, so I know
that the technology exists to do this, I am just not sure
if Access can handle it.

Thanks in advance for your help. Please respond to my
personal email with your suggestions.

Dave

 

[John Nurick]

Hi Dave,

Access can handle it, but not in precisely this way.

It sounds as if you want to store information about entities (you don't
say what they are so I'll call them E) which have among other things a
large and perhaps varying number of yes/no attributes. The way to do
this in a relational database such as Access is to use three tables,
like this, to implement the many-to-many relationship between Es and
Attributes:

tblE
ID - primary key
other fields for information about E such as
name, date, whatever, but not the 400+ checkboxes

tblAttributes
Label - (Primary key) what you now use as the label of
the checkbox.
This may be the only field you need in this table,
whose purpose is to provide a list of the attributes;
but if you want do display them in anything other than
alphabetical order you'll need to have a second field
SortOrder - Number (Long)

tblEsAttributes
ID - foreign key into tblE
Label - foreign key into tblAttributes
(both fields form primary key).

At present, the fact that the E whose ID is 999 has the Attribute XXX is
stored in your table by checking the checkbox labelled XXX in the record
whose ID is 999. In the relational structure, it is stored in a record
999, XXX
in tblEsAttributes; if E 999 doesn't have this attribute, there's no
such record.

Among the advantages of this approach are:
-there is virtually no limit on the number of attributes;
-if you need to add new attributes, you don't have to modify the design
of your tables, you just add records to tblAttributes.
-what you are now storing in the checkbox label is automatically
available to queries.

The simplest way to create a user interface for this in Access is to use
a form bound to tblE, with a subform on it bound to tblEsAttributes. On
the subform, use a combobox whose RowSource is a query on tblAttributes.
Instead of using checkboxes, just create or delete records on the
subform.

 

[John Vinson]

Comments inline.

I am attempting to create a checkbox form that contains
205 checkboxes.

I'd suggest a normalized table design (see below) with - if this is
the interface the user wants, which seems strange to me! - an
*unbound* form with code to update the table.

What I need to do is set up the form so
that when the checkbox is checked, the box's label is
stored in the underlying table for later merge into Word.

Is there a one-to-many relationship between this form's Entity and a
list of 1 to 205 keywords? Or do you want to build up a paragraph
containing 1 to 205 words? If the former, a second "many" side table
would be suitable; if you want the paragraph, a single memo field with
code to add words to it might be suitable.

The problem is that my design calls for two fields for
each check box. One field binds the "checkbox" and the
other field binds a hidden text box that stores the
checkbox's label when the box is checked. Since I have
205 checkboxes and 205 attached invisible textboxes for
storing the checkbox labels, I am unable to create a table
because Access limits its table and query fields to 255.
Does someone out there have a workaround for this
problem? I have struggling with this for over a year and
although I have consulted "experts" from around the globe,
none have been able to come up with a workable solution.
To compound the problem, I need to have a query that links
two tables (from two different forms) and that makes the
grand total of fields to over 550!

I suspect that one field, or two fields on a subform, will be ample.
Please explain a bit more about what will be done with this data once
it's entered - there are undoubtedly a couple of good solutions which
don't involve hundreds of fields!