Report on selected checkboxes (1 Viewer)

I've done some searching around and have found posts that are very close, but not the same as what I'm trying to do.

Table Columns:
Client_ID
Product_1
Product_2
Product_3
<continues to about 30 products>

I use checkboxes on a form to capture (values "-1" or "0") whether or not they want the products. Then I want to report on only the products they selected. For example:

Report:
Client ID: 12345
Product 3
Product 5
Product 9
etc...

I think I may have structured the table incorrectly in that I want the product name to be written on the report, but I named the columns the product name and the only thing written to the record is a "-1" or "0".

Should I restructure the table? Or can I check for a value of "-1" and write a variable name to the report?

Any thoughts?

Thanks!

>>>> any thoughts<<<

Yes, you do need to restructure your database.

Have a read here there's text and video instructions and also a free tool for converting your existing problematic table into something you can use in the correct manner...

https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access



Sent from my SM-G925F using Tapatalk

I think this pointed me in the right direction, but still not exactly what I'm looking for. The link did help on change my thoughts on the table structure. I think where I need to go with it is create a Product table:

Product Offering Table:
Product Description
Product Code (primary key)

Where I'm struggling is how to capture the unique selections from checkboxes on a form. I think I would then create a Client Product Selection table that would link the selected checkboxes to the Product Code. The problem is, checkboxes only store "-1" or "0" values and I can't link the Product Code with the client selected product.

If I can store the selected products into a client selected table, then I can easily query them and report on them. I think I would create a new table:

Client Selected Table:
Client ID
Product_Selection <- this is my problem - how to link checkbox selection on a form to a product code?
Product_Code

Thoughts?

IF you only store the selected products you don't need a Selection yes or no. Storing that they don't want something seems not the way to go.

What if they make changes? How would I know? Or is the best option to clear (via delete query) and then rewrite (via append query)?

I'm still struggling in my head to understand the best method to write the product selections to the client selected form.

---- Client Form -----
Which products do you want?

[] Product A
[] Product B
[] Product C
[] 30 more...

Then write their selections to a client selected table:
Client ID
Product_Selection <- one record per checkbox
Product_Code <- tied to Product Offering Table

Do you want something like this?

0 - Adding a Checklist to MS Access


If you do see the youTube Playlist HERE:-

Automatically add a Checklist to MS Access

Thanks! I was trying to do this without coding, but in the end it had to be done. I was able to write the values to a separate table with if statements after creating a new module.

Essentially I wrote an if statement; If checkbox = "-1" write record to client product selection table. Now I can generate a report based on my query of the table records created out of my module. It's working as expected, but I still feel like it's not designed well. For now, it will work! Thanks for your help!

If your list of items isn't too big an alternative would be using a multi-select list box which you can populate from your table of Products.

You could then click the items they want and then add a command button to loop through the selected items to add them to your required list.