Too many checkboxes to handle

[goldybobble]

Hello,

I had an Access database that would classify items with one classification, and then classify one sub-classification based on the selection for the main classification.

This worked well, but did not provide the granularity that was needed. What I needed was to have multiple selections available for each sub-classification, and the ability to select as many of these “sub-sub” classifications as applicable. I set out creating tables for each sub classification with the identical fields in each table: ItemID, ClassificationType, Classification SubType, then a series of unique checkboxes for that sub classification type. I used a tree-view control where the classification types were the 1st level, and the sub types were the nodes. Clicking on a node opened a subform with all the check boxes from the corresponding table, that allowed for easy data entry.

Now I am stuck trying to combine the data from each table so that I can do trending based on the Classification type, or sub-type. I tried by doing a union query to combine the ItemID, ClassificationType, and ClassificationSubType fields from each table and put them in a new table. I then used that new table as a basis for combining all the subtype tables together. The problem I have now is when I change the criteria for the query to show only a specific classification sub type, the correct records show, and the corresponding check boxes are checked, but I end up with a huge number of blank columns- all the checkbox columns that correspond to other sub-types that I am not interested in for this particular query?

Eventually, i want this query to be based on a multiple selection list box so that I can choose (for example) subtype A,B, and D, and have only those corresponding records show.

Can anyone kick me in the right direction?

Thanks!

Ben

[Stewart Ross]

Hi Ben. Problem is that you have defined separate tables for each sub-classification instead of containing all of these in one table with an additional attribute specifying the sub-class type. The giveaway is when you wrote that the tables are identically structured.

In relational table design it is quite possible to have relationships where a table relates to itself. It isn't commonplace, but it does arise quite naturally (for example, a 'managed by' attribute of a staff table, as managers are also staff themselves.) This is the case here; a class relates back to a class through a sub-class attribute of some kind.

In Access such recursive relationships can be set by dragging two copies of the same table into the relationships window and setting a relationship from one of the copies to the other (Access cannot show or set a recursive link in the standard notation of a loop line linking fields to and from the same table).

You will need to resolve your many-table approach to subclasses and merge these as appropriate before you will resolve the difficulties you are experiencing. This is a fairly large task as it involves a complete rethink of your current approach. It is worth doing, however, as you will not be able to exploit the power of relational queries with your current design, no matter what you do.

-Stewart

[goldybobble]

I understand the issue, and i actually have a classification type and sub type field in each table set to default to the sub type that that field's checkboxes are based on so that they can be related. Once i did the merge, i effectively have one large table with all the records. i know that it wasn't ideal, but it allowed the check box fields of each sub type to be seperate tables, instead of burried in one massive table. If i take that new table that was created as a result of the merge, would i be able to do that recursive relationship? I could even save the table, and then use the massive combined table in the future insead of the individual tables. The issue i am still having is that with the massive combined table, there are 3 fields that are the same... [ID] [Type] [Subtype] but following that are 300 checkbox fields, with certain checkbox fields pertaining to one subtype, and other checkbox fields pertaining to another subtype.

I am still struggling with designing a query that will be able to show me only the checkboxes of the subtype i am interested in, and not all the other blank/null fields. They are null because they pertain to different subtypes that i am not querying for.

Any thoughts?

Hi Ben. Problem is that you have defined separate tables for each sub-classification instead of containing all of these in one table with an additional attribute specifying the sub-class type. The giveaway is when you wrote that the tables are identically structured.

In relational table design it is quite possible to have relationships where a table relates to itself. It isn't commonplace, but it does arise quite naturally (for example, a 'managed by' attribute of a staff table, as managers are also staff themselves.) This is the case here; a class relates back to a class through a sub-class attribute of some kind.

In Access such recursive relationships can be set by dragging two copies of the same table into the relationships window and setting a relationship from one of the copies to the other (Access cannot show or set a recursive link in the standard notation of a loop line linking fields to and from the same table).

You will need to resolve your many-table approach to subclasses and merge these as appropriate before you will resolve the difficulties you are experiencing. This is a fairly large task as it involves a complete rethink of your current approach. It is worth doing, however, as you will not be able to exploit the power of relational queries with your current design, no matter what you do.

-Stewart

[Stewart Ross]

... but following that are 300 checkbox fields, with certain checkbox fields pertaining to one subtype, and other checkbox fields pertaining to another subtype....

which tells me again that the table has not been designed relationally. It is certain that if you have that many fields you either have repeating groups of some kind, or entities which should be separated. In other words, your design is not likely to be in first normal form never mind third normal form / Boyce Codd normal form.

Relational database design rarely leads to tables with more than 50 fields, even in complex systems. The norm is for no more than low teens to twenties of fields in any one table, but many inter-related tables.

We have an excellent HowTo article introducing database normalisation and table structures, which might help you here.

In answer to your question on the merged table, yes you can set a recursive relationship after merge, but I would hold off on that until you resolve the normalisation as it will undoubtedly lead to what is in one table at the moment changing to more than that afterwards.

-Stewart