How to display multi-select screen with 100 choices

[DrChocolate]

Greetings: Here's the background: Using Access 2010, my overall project is to construct a database keeping track of support group data. Each group (e.g., Alcoholics Anonymous) is assigned to one or more categories (e.g., Addiction-Drugs, Addiction-Alcohol). The website will hopefully used MySQL or something similar to take advantage of the Access table structure.

Here's the relevant table structure: It's many to many (groups and categories) here, so I've got one table for overall group data, one listing categories, and a junction in between using a GroupID and a CategoryID to carry me through.

Now the question: When I'm entering a new group, I allow a choice of one or more categories. I'd like to display them in multiple columns across the screen where each chosen category "lights up" or somehow shows that it is "on" (or chosen). A left click on a category name would toggle "chosen" and "not chosen". (By the way, a right click might hopefully display a list of tags associated with that category.) The display design will be important so that I can look at the list and see very quickly and clearly what's on and what's off as I interview the group representative on the phone.



How do I construct a form that gets as close as possible to my desires? Thank you.

[June7]

100 categories? 100 'columns' to toggle? Sounds quite impractical, especially for a web design. But I have never built a data driven web site so maybe I am way off on that.

[DrChocolate]

Dear Writer of First Reply,

Thanks for your reply. I apologize for being unclear. Each of the 200+ groups can be associated with one or more of about 120 categories (generally no more than 5 categories per group).

I'm still new at this, but I spent most of the day trying to find something in Access that makes this multi-select issue easy. I wasn't very successful. Apparently listboxes do not work with multi-select options unless you stick in an intervening table. I have too many categories for a combo box. One could use check boxes (and write the VBA to retrieve the results to add to the relevant tables), but that doesn't deal with editing and inactivating a group-category relationship. The best solution I could come up with is a series of buttons (each one showing a category name) with some clever VBA code (sorta like an action query) that goes into action every time you press the button for a category making toggling on/off possible; one could make the display graphically clear by putting a text box nearby each button which would change color and wording (e.g., "on" or "off") depending on that category's current status for this group.

Good grief...little did I know that a multi-select list would create such a problem. I'm even more discouraged by the expert responses to similar questions noted in the links at the bottom of this thread noting that Access was not designed for this kind of problem.

Before I started this project I was warned that when Access works, it's lovely; but when it doesn't, it's much trickier to make up work-arounds for it than for most other programs. I'm coming to the conclusion that Access may not be the best route to go for this particular application.

Thanks again for taking the time to write. I hope that someone else comes up with a more efficient solution; when things get this complicated I begin to wonder about stability.

100 categories? 100 'columns' to toggle? Sounds quite impractical, especially for a web design. But I have never built a data driven web site so maybe I am way off on that.

[DrChocolate]

Dear Nellie,

Two solution have presented themselves I believe.

One is to use a screen full of check boxes, but those little boxes are hard to see, not very pretty, and not even approaching cool.

So I imitated what I wanted the check boxes to do by using pairs of toggle buttons to the left of a text box containing the category name(s). This would be repeated in rows and columns to allow a display of 100 categories easily.

When I click (or touch) on the toggle button, my color settings make it toggle back and forth from green to red (or whatever). That clicking changes the value of a check box in the table (after saving the form). This screen design will make it really easy to see at a glance, gives me the ability to display a group's list of categories before editing, and, most important, looks cool.

The only thing left to try is the table structure -- if I make a 2 dimensional table it should work given that during editing, the group ID will stay constant and the category name will change. That means (I think) that the group names will go across the top (columns) and the categories will go down on the left (rows). As I move from button to button on the screen, the "record" number will change (or here the category name), but the group number (the columns) will stay constant.. I will draw the contnts of the text boxes from the category names in the table. That will make it easy to modify or add categories.

I suppose I might be able to change the back color of the text boxes to match the toggle buttons, but that would be visually confusing -- something has to stay constant.

If any of this makes sense, and you have any idea how to make this simpler or if you see a problem, I'll be wide open to your ideas. Thanks.

100 categories? 100 'columns' to toggle? Sounds quite impractical, especially for a web design. But I have never built a data driven web site so maybe I am way off on that.