checkboxes on form to run query

[garymilam]

Need help with checkboxes on form. I have 17 categories in a table with dates in the fields. I want to have a search form with 17 checkboxes so a user can check a checkbox to get the date, or check more than one checkbox to have multiple dates for the corresponding category. for example, one category is red training, one category in blue training, one category is white training all with dates of completion. on the form I check red training and get the name of all people who completed red training and show the date, and does not show the other training, or I check red and blue training, gives me the name and the dates completed. Thanks for anybody who can help.

[PhilOfWalton]

Probably not the neatest way of handling things. What happens when the 18th category gets added - a complete re-design.
#I would suggest a multi-select listbox based on your categories (that way the number is irrelevant)

You then build a criteria based on the ItemsSelected from your list box and apply that as a filter.

Sorry this is a bit vague, but a little more information on your table structure would help.

Phil

[zmbd]

IMHO: your database could use a redesign now:
Database Normalization and Table Structures

Under normalization you most likely should have one table holding the Employee information, one table holding the category of training and a third table that relates the two tables with dates.

You can still use a multiselect List box as Phil has suggested or another approach is to use subforms, for example have a Parent, Child, Grandchild arrangement (see image here:) with the Employee at the Parent level, the Categories as the Child and Dates as the grandchild... or just parent as the Employee and the Category with dates as the Child subform. Or a multitude of other designs that in the long run would make upkeep on your data much more efficient.

[garymilam]

Table Structure:

Name        Red Training     Blue Training    White Training
Doe, John   4/4/16           3/5/16           1/9/16

I would like to let the user select one, two or all of the training categories and the query run to a report. But if the user selects Red Training, Blue and White Training doesn't show.

[zmbd]

Very much a candidate for Normalization as mentioned in my last post.

We'll have to see the code you are using to create your report.

[PhilOfWalton]

Agree with zmbd.
I think you need a table of Employees, a table or Categories and a Join table with a combined key of EmployeeID and CategoryID and a CompletionDate. Whether the CompletionDate is part of the key depends on whether it is conceivable that John Doe could re-do his Red Training and have 2 different completion dates. On the assumption that this could NEVER happen, then CompletionDate is not part of the key.

Phil

[garymilam]

So should I have 3 tables or two?

Table Employees
ID
LastName
FirstName

Table Categories
Red Training
Blue Training
White Training

Table Complete Date
Completion Date

[zmbd]

Personally I would go with three; however, I don't know your workflow.

My design in part might be something like
(where: PK = Primary Key, FK = Forgien Key):

t_employee
[pk_employee] autonumber
[employee_Lname] shorttext(50)
[employee_Fname] shorttext(50)
[employee_badge] shorttext(15)
[other fields and related FK]

t_categories 
[pk_categories] autonumber
[categories_name] numeric(long)

t_traininglog
[pk_traininglog] autonumber
[fk_t_employee] numeric(long) enforced relationship t_employee
[fk_t_categories] numeric(long) enforced relationship t_catagories
[traininglog_datecompleted] date

In t_traininglog I have not constrained the table to prevent duplicated entries ([employee],[category],[datecompleted]); however, at minimum, I would advise one to create an additional composite index to prevent duplicating the same [employee], [category],[datecompleted] record entry.... or you could create an index that prevents a duplicate of the employee, category if the employee was only required to take the training once in the service time of the employee. Of course, one can add logic to a form (and since Access2013 - table level Macros) to prevent this from happening; however, I believe that the simple composite index is sufficient, elegant, and works even when Macro/VBA is disabled.

>> Why not use these additional indexes as the primary key...
Personal choice.
I very strongly dislike composite Primary (aka Natural) keys in tables as these types of Primary Keys make establishing inter/intra-table relationships fiddly, creating select and update/append queries become needlessly complex, and last (but not least) Macros and VBA just do not handle them as gracefully as the arbitrary surrogate primary key. (also, I am in the group that holds that primary keys shouldn't normally have any "real world" meaning... there are some small exceptions such as the periodic chart of the elements, Fe, will be Iron, and Ca will be calcium :) but then again, I use the atomic number :-) )

>> Of Note: the "autonumber" datatype should NOT be relied upon for anything meaningful such as serial or sequential numbering. Autonumbers have one and only one use, to uniquely identify the record.

We're way off topic here so let's move back,

you will need some code at runtime to loop thru either the t_traininglog and the related tables or a query (either in the VBA or as an Access Object) and set your check boxes... but what happens when you add a new category, then you have to redesign the form.

A Parent/Subform arrangement might be a better choice, and most likely will not need to be redesigned every time you need to add a category.

The attached image is an example of one that is a bit more than you might need as it has a parent form and two subforms with the second subform tied to the first subform. However, once can move to the correct parent, and either select, update or enter a new child. The child record once selected will filter out the grandchildren... etc....

Using this arrangement, no code is needed to create the entries. Simply move the parent form to the correct Employee, the related training shows up in the subform, and you can make your entries there.
In the subform, use a combobox bound to [fk_t_categories] with a record source based on the t_catagories showing just the [categories_name]

[garymilam]

zmbd,

Thanks, I will read this over and see what I can do. This DB I will use to track training for my company. My ultimate goal is to be able to query the records for certain training with dates. I would consider myself a beginner/intermediate at access, but I really enjoy using it, so I'm really trying to learn more and more. I'm creating several databases for my company, I just started there last November, and they have some processes that need updating. I really, really appreciate all the help.

Gary

[PhilOfWalton]

Please excuse the crudeness of these two images.
Categories.png shows my member form (equivalent to your Employee form) and my interest subform (equivalent to your Categories subform).
As you can see there is a combo box to select my interests. Your combo box would select your category.
Where I have a comment, you would have a completed date.

The Categories2.png shows the situation the other way round. In other words in my case which members have a particular interest.
So in your case, you would select a category, say White Training and it would show all those who had done the course and completion dates where applicable.

Phil

[garymilam]

PhilOfWalton, could you try to send me the pics better, they are real fuzzy. I really want to use your send category you sent. Select a training and show who has taken it. Thanks for your help.

[PhilOfWalton]

Am having problems getting clearer pictures. If you let me have your email, I can send them as attachments

Phil