Tracking Training (1 Viewer)

Wulf13

Registered User.
Local time
Today, 15:12
Joined
Jul 6, 2004
Messages
85
I have a DB I'm using to track training for assigned people. Right now the training gets put in the DB at the end of the day by going through each persons record and adding the training that they did for the day. As you can see when training 30+ people a day, putting this in the system can take awhile. I'm trying to find a way to do a mass input to cut down time and could use some help with this.

My tbls are as such,

1st Table:EMPLOYEEtbl
EmployeeID
EmployeeName
(Other information)

2nd Table:CLASStbl
className
classID

3rd Table:CompleteClasstbl
EmployeeID
classID
DateTaken

What I like to do at the end of the is select the ClassID/DateTaken items once and then type in a just the 30+ list of employeeID's click a button and append it to the my 3rd table. Of course writing it out like this makes me think it should be easy but for some reason I'm lost. Does anybody have any suggestions?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:12
Joined
Aug 30, 2003
Messages
36,129
One way would be a multiselect listbox for employees. Searching here on multiselect should turn up the code to append a record for each one selected.
 

Wulf13

Registered User.
Local time
Today, 15:12
Joined
Jul 6, 2004
Messages
85
Wow...don't I feel stupid

Paul-thank you for pointing me in the right direction.

I should have looked at this topic sooner, I've always ingored this 'multi-select list box' that I hear about. Yet now I find it is pretty much what I need. I've looked at several examples...Miss Hartman's being the best. Pat's example works wonderfully for adding new records, but what about updating the old ones.

Example:

Joe is completed the training class for the first time, well Bill has completed it for the 2nd time. All I need to show is the latest completion date for that specific course for both people.

I'm thinking about using the multiselect box in conjunction with an unbound text box to delete any previous records, and then run the add new record portion to replace/add todays courses. My only concern is I'm making this harder than it has to be....any thoughts
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:12
Joined
Aug 30, 2003
Messages
36,129
I guess it depends on what you want. I can see wanting to track if a person has taken a course more than once. If you don't, then you could certainly do a delete before the append (probably within the loop).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:12
Joined
Feb 28, 2001
Messages
27,253
First, Pat Hartman (referenced earlier) is a SHE. Second, I have variant thoughts about your problem as described.

In the general case (which may not apply here, I admit), there is limited value in trying to find a faster way to input the data you describe because you will add another layer to something that cannot be shortened - raw, general data entry. Having said that, I won't abandon you, because there is a chance that "less general" cases might have short-cuts.

There IS a possible way around this but it takes a bit of doing - and here is where your comfort level and the amount of work you are willing to put into it will govern just how useful this suggestion might be. (That is the problem - not that it can't be solved but whether it is worth the effort required.)

Create a table (if you haven't already) that lists training subjects. Be sure it has a viable prime key, perhaps an autonumber or a course catalog number or something (anything) unique and SHORT. Whatever else is in the course catalog is up to you. Unless the course number changes with different presentations of the same material, the date of a presentation should NOT be a part of the table. (Make a separate child table of this table for tracking presentations if you need to track such a thing.)

Create a junction table that shows Employee ID, Course ID, and date. Maybe that is all that is required (unless there is an Exam associated with the course and one can get a numeric or pass/fail score). If the course ID changes with date, you can omit the date. (But that is not a design I would recommend.)

Add a "scratch" Yes/No field to the employee table for "TrainingMark" or something like that. Be sure that your employee table has a unique ID for each employee, whether it is autonumber or corporate employee number or some other short field. I say "short" because you want to use the prime key of the employee table, and PKs should always be as short as possible to still get the job done.

Now build a form with a single drop-down list box for course names (from your course table) and a sub-form in CONTINUOUS MODE based on a query that shows the employee and this check-box. Not really important whether the continuous form shows anything else.

Put a button on the form to run a query to reset all employee TrainingMark fields to FALSE. (Update query of the employee table, of course.)

Put another button on the form to run an APPEND query that takes the selected value from the list box and APPENDS an entry to the junction table, one entry for each person with the TrainingMark set to TRUE. The entry should fill in all required fields - Employee ID, Course ID, date, whatever else you need.

Added wrinkle: Add a field for score or for pass/fail to the employee table if you need to track scores. This score is NOT permanent and is merely used for convenience. Further, the score or pass/fail marker has NO MEANING for any employee for whom the TrainingMark isn't TRUE.

Added nice touch: Make the UPDATE TRAINING HISTORY button automagically go back and erase the Employee fields for TrainingMark and the score or pass/fail data when done. If you have these actions as queries, you could make the button run a Macro - or you could write the macro and then convert it to VBA and run the VBA code under the button's OnClick routine.

Now open the form, reset the marks, scroll through your sub-form to check off the employees who attended training, select the drop-down for the course number, and when all employees are selected, click the button that does the update. If you have more than one class per day to update, you do this once for each class, checking the boxes for each attendee as appropriate for each course.

Like I said, a little tedious, but if you are willing to work a little, it would work fine and would be (fairly) robust.
 

Wulf13

Registered User.
Local time
Today, 15:12
Joined
Jul 6, 2004
Messages
85
First of all, my apologies to Miss Hartman, I've made an erroneous assumption. I've corrected my previous post.

Second of all, Doc Man, my first impression of your post is it seems rather daunting and confusing. I shall have to print it out and take it with me to read while I'm at lunch to get a better feel for it. I shall get back to you on this. Thank you for your post.
 

Users who are viewing this thread

Top Bottom