Have a list of foods in a table and a list of family members in another, not sure how to make a check list. (1 Viewer)

[Bettany]

Hi, I have 500 foods in a table, and the goal is for each family member to be able to go down the list and check the foods they like using a form. So essentially I need a form with the 500 foods listed, a checkbox next to each, and to be able to indicate somewhere on the form, who the person checking the checkboxes is (I wouldn't want them to have to list their name 500 times). I'm confused as to how to approach from a setup perspective. I tried creating a new query to bring in the foods and the familymember table but I got a cartesian product and was still unaware as to how to add the checkboxes. Thank you!

 

[theDBguy]

I think to get the ui you described, you would either use an unbound form or a temporary table.

 

[Gasman]

I would probably have a combo, that breaks the foods down into categories, meats, fish, veg etc.
Then a listbox that uses that combo value as criteria. Listbox would be multi select.
The user name would automatically be selected as I would ask them that when they started the DB.

Then I would run a query from a command button to populate a table that held those links. I would delete all previous links for that category before adding the new ones. So a few button presses, one for each category used.

Then take it from there, with any issues.

 

[ebs17]

Start with a many to many Relationship between the foods table and the familymember table.
The form design then derives from this relationship.

 

[Pat Hartman]

You would NEVER use an unbound form with 500 checkboxes in a relational database. You need to add the junction table as suggested by ebs17. Then you need to use a subform. Each row in the subform reflects one choice. You pick the choice from a combo.

Here's an example of a m-m implementation. It shows two ways of looking at the relationship. One using a subform and the other using a popup.

 

[GPGeorge]

Hi, I have 500 foods in a table, and the goal is for each family member to be able to go down the list and check the foods they like using a form. So essentially I need a form with the 500 foods listed, a checkbox next to each, and to be able to indicate somewhere on the form, who the person checking the checkboxes is (I wouldn't want them to have to list their name 500 times). I'm confused as to how to approach from a setup perspective. I tried creating a new query to bring in the foods and the familymember table but I got a cartesian product and was still unaware as to how to add the checkboxes. Thank you!

Perhaps you would be well served by investing time in learning how to design tables and the relationships between tables in a relational database application. What you propose (500 checkboxes) is not going to work very well no matter how you try to implement it.

Instead, you need to follow Pat's directions on setting up the tables and then the proper forms to work with them.

Here's a brief video that should get you started.

 

[Gasman]

You would NEVER use an unbound form with 500 checkboxes in a relational database. You need to add the junction table as suggested by ebs17. Then you need to use a subform. Each row in the subform reflects one choice. You pick the choice from a combo.

Here's an example of a m-m implementation. It shows two ways of looking at the relationship. One using a subform and the other using a popup.

No example attached Pat?

 

[Pat Hartman]

Sorry,

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Bettany]

Pat, that solution means a lot o

You would NEVER use an unbound form with 500 checkboxes in a relational database. You need to add the junction table as suggested by ebs17. Then you need to use a subform. Each row in the subform reflects one choice. You pick the choice from a combo.

Here's an example of a m-m implementation. It shows two ways of looking at the relationship. One using a subform and the other using a popup.

Thanks Pat, from a usability standpoint that seems extremely painful. To be sure I have this right, your solution involves clicking a combo box in a subform, scrolling through 500 options in that combobox, selecting an item, and then repeating that process, potentially 500 times?

 

[Bettany]

I think to get the ui you described, you would either use an unbound form or a temporary table.

Kind of inclined to agree here. Any UI setup that doesn't have all the of checkboxes available to be clicked just seems too painful and repetitive (particularly combo boxes). Is your idea to have all the foods listed next to the check boxes on a single form, and to use VBA to tie all the info together?

 

[GPGeorge]

Kind of inclined to agree here. Any UI setup that doesn't have all the of checkboxes available to be clicked just seems too painful and repetitive (particularly combo boxes). Is your idea to have all the foods listed next to the check boxes on a single form, and to use VBA to tie all the info together?

"...each family member to be able to go down the list and check the foods they like..."

To be honest, asking anyone to select 500 checkboxes also seems a bit over the top to me. No matter how you present that interface, with checkboxes, combo boxes, etc. 500 selections for anything is just plain overwhelming. Not to mention finding and changing a selection later when they change their minds.

There are other ways to do this, but the one that any experienced relational database developer would recommend is going to be based on the many-to-many approach previously recommended by others here. I know, you don't have to do that, but sooner or later the price of not doing it is going to seem too high.

But then there's another way to do this, as well. Two in fact. One is to include categories of food, and foods within those categories:

Meat, Fish, Poultry
Dairy
Fresh Vegetables
Fresh Fruits
Condiments

and so on. You'll still have hundreds of foods in some of the categories, but that's a more user friendly strategy already.

I suggest, once again, you invest time learning how relational database applications work. Also do a search on Cascading Combo Boxes to get some insights into more user friendly interface designs.

Obviously, you have the final say, but since you came to this forum to ask for advice from experts, that's what you are going to get, expert advice.

 

[bastanu]

You could also consider having two multi-select listboxs side by side in the classical Available<->Selected setup and implement code in double-click event to move from one side to the other in conjunction with the four usual buttons (<-,->,->>,<<-).
Cheers,

 

[GPGeorge]

You could also consider having two multi-select listboxs side by side in the classical Available<->Selected setup and implement code in double-click event to move from one side to the other in conjunction with the four usual buttons (<-,->,->>,<<-).
Cheers,

That's probably the approach I'd take too, but 500 rows in a list box? Daunting. There has to be some pre-filtering to get the list down to a manageable size.

 

[Uncle Gizmo]

your solution involves clicking a combo box in a subform, scrolling through 500 options

Edit:-
My method solves the aforementioned problem ...

500 is a lot of choices! Might pay to group the choices in some way.

I did a blog about check lists here:-

Add a Check List to your MS Access Database - Nifty Access

Add a Checklist to MS Access Checklists are very useful. However, they can be a pain both for developer and for the user. The last thing you want is a subform comprising of a set of of a combo boxes that the user has to select in order to provide the question and the answer!

www.niftyaccess.com

Might be of interest.

I also demonstrate how to setup checklist groups here:-

Add a Multiple Check List to your MS Access Database - Nifty Access

Multiple Checklists This set of videos I expand on the idea presented in the "Single Checklist" videos. I recommend you watch the "Single Checklist" videos before you start creating"Multiple Checklists". You will need to be able to set up a "Single Checklist" before you embark on the task of...

www.niftyaccess.com

But you need to setup a single checklist first, to gain insight in how multiple check lists work....

If you can't find that blog, let me know. ..

This YouTube maybe of particular interest:-

 

[Pat Hartman]

In addition to dividing the list by category, which reduces them significantly, you can also remove the selected items from the lists as they are selected so that they grow smaller with each selection. You choose Tuna and it doesn't appear in the combo when you move to the next line to choose something else. Unless you don't know the name of the food you are searching for, you wouldn't scroll the list, ever, you would type and Access will reduce the list. For each character you type, the list also gets shorter. If there are only three items that start with T, as soon as you type a T, the list immediately reduces to three items.

Jet and ACE limit the number of columns in a table to 255 so you can't use a single table with 500 columns, nor can you make a query with more than 255 columns. Therefore, you are going to have to denormalize your schema from the get go building your app on a shaky foundation. You are allowing your vision of an interface to color your schema which is also a bad plan.

If you don't believe the expert advice, I suggest that you actually make a table with a hundred columns. Build a form. Now try to do anything with that table, you will quickly see that you have to create huge queries by the hundreds. Then if you discover you left out an important item and so you want to add it to the table. Sure, it's easy enough to add it in the alphabetically correct position in the table, but go fix your forms and reports and see how you like that. Create a query that selects how many people like roasted chicken and carrots. You can't use the same query if you look for Ham and sweet potatoes. You have to write a new query. That doesn't happen when you normalize your table. You can use a single query with arguments. This time the query looks for x and y, the next time it looks for a and b. Then another to find three foods that everyone likes. You will rapidly exhaust yourself and find that this plan is just not workable.

You can then revert to a standard, normalized schema but to produce your preferred interface, you now need to use an unbound form. Sharpen up your VBA skills, you're going to need them.

@Uncle Gizmo 's solution is a little different. For his solution, each time you add a new person, You append all the possible food choices from the master list. They are displayed in separate subforms which group them by type. So, subform 1 shows meat, subform 2 shows vegetables, etc. You could also use a tab control so that it is easy to stack the subforms to reduce the size of the form. With this solution, you choose, using a checkbox. This method most closely resembles the presentation solution you are looking for but it still uses properly normalized data so you can create parameter queries rather than hundreds, perhaps thousands of specialized queries. It may be the compromise you need. The one problem with this solution, and it is not insurmountable since I have used the solution myself is that when you add a new food item to the master table, you must, in the AfterInsert event of the form, run an append query that appends that single new food item to the table that shows the foods assigned to an individual. This type of table, is typically very small since it only has a PK, two FK's (one for the person and one for the food) and a checkbox. But it does mean that you rapidly end up with a very large set of rows in the junction table which is used to map foods to people since each person will ALWAYS have the same number of rows as there are food choice options. So, Sue has 500 rows, John has 500 rows, Nancy has 500 rows, etc. I can't see this being a problem for you. It might be if you were managing food choices for thousands of people but not for a family. The solution I recommended only adds rows to the junction table for "checked" items so the row count will be much smaller.

Now that I think about it, Tony's solution might not have to add all items to the junction table. It might be able to use a left join from the master table to the junction table and the act of selecting the checkbox will cause the row to be added to the junction table. That way you get the best of both worlds, an interface you like with the most efficient data storage option.