Display Field in report if Check Box is true. (1 Viewer)

Laser1962

New member
Local time
Today, 12:15
Joined
May 11, 2018
Messages
5
I have a table that lists all of the required training classes taught in house by our training department for each employee. Each record in the table contains the employee name and yes/no check boxes for each of the 20 or so classes that is taught as to whether they are required by the employees job function. I would like to create a report that lists each employee and the required classes they are required to take, without any blank spaces. Rather new at access, but I have done a little VBA programming if necessary.
 

isladogs

MVP / VIP
Local time
Today, 19:15
Joined
Jan 14, 2017
Messages
18,186
For each employee, are these classes 20 or so separate records with a single class field and yes/no field or 20 or so separate fields?

Hopefully the first of these options
 

Beetle

Duly Registered Boozer
Local time
Today, 13:15
Joined
Apr 30, 2011
Messages
1,808
... Each record in the table contains the employee name and yes/no check boxes for each of the 20 or so classes that is taught as to whether they are required by the employees job function...

Before you begin to get involved in form/report design, I would suggest you rethink this table design. This is going to continually cause problems when you try to design/implement other aspects of your application in the future.

If you go to this page you will find some good information on this specific problem (using check boxes to store choices), but I would also suggest you do some research on relational design and normalization. What you have is a many-to-many relationship between Employees and Training, or perhaps Job Types and Training. You need three tables to properly model this relationship.

As an example of what i mean by future design problems, let's suppose that a new training class gets added to the mix at some point in the future. With your current design you will need to do the following;

1) Add the additional necessary fields (check boxes) to the table (which is really data, not fields).
2) Redesign every query that uses that table as a record source, selecting the additional fields and adding additional criteria to the where clause.
3) Redesign every form or report that uses any of those queries (or the table) as a record source, adding additional controls to represent the new choices.
4) If your application uses any VBA or macros that that reference the certification type fields, you will need to find all those instances and rewrite that as well.
5) Probably a few other things that aren't coming to mind right now.

Situations like this can quickly become a design nightmare. It would be better to correct this early in our overall design process, rather than later.
 

Mark_

Longboard on the internet
Local time
Today, 12:15
Joined
Sep 12, 2017
Messages
2,111
Sounds like you are missing a few tables.

Employee -> People who work for you.
Positions -> What people do for you.
Training -> Training (including in house) required for positions including how long training is good for.
LINKING TABLE PositionToRequiredTraining -> What is required for each position
LINKING TABLE EmployeeToRequiredTraining -> Links a given employee to a given training requirement for their position. Includes date training was completed.

There may be more tables required. This should allow you to set up not only who needs what training, but when they took it and when they need to retake (if required).

Even if you don't need all of this at the moment, as soon as you get involved in ANY training that expires you will need this structure. Best to have it in place and not need it all than need it and not have it.
 

Users who are viewing this thread

Top Bottom