Re: Table design - field limits

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 07:34:23 +0100

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.

On Sun, 27 Jun 2004 14:36:52 -0700, "dave" <davedenn@sbcglobal.net>
wrote:

>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 [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Re: How to modify label.text in a dynamically generated label in VB.net
    ... hybrid control that contains a checkbox and a label. ... That frees up the tag ... CheckBox control and the value was the Label control. ...
    (microsoft.public.dotnet.languages.vb)
  • Inconsistent CheckBox Color -- Anyone?
    ... Hi...sorry, over 30 days, so I've included the original thread below, ... but when I move from the control - ... I set vb code so a checkbox on my form enables its label to change ... CheckBox control changes. ...
    (microsoft.public.access.forms)
  • Re: how to have a checkbox change color and text of a label control?
    ... So could you make the label control box change color instead of the font? ... states "Private Sub Form_Current is highlighted yellow ... Name the checkbox and the label with similar names. ...
    (microsoft.public.access.forms)
  • Re: Access: make checkboxes editable not just border
    ... Place this label where you wish to see the check mark. ... If [CheckBox] Then ... If you want a CheckBox field label then just add another ... This post is a suggestion for Microsoft, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Form Design with Linking Table
    ... PK_IssueID (autonumber, primary key) ... all AreaNames with a checkbox next to each, so that the user can mark ... which AreaNames are associated with the issue he's adding/editing. ... I've tried creating a subform, ...
    (comp.databases.ms-access)
Loading