Schedule Training

[hikerdood]

I created a training database thanks to the help from this site.



Loaded into the database is an Employee Table and their respective departments along with a Course Training Table.

All employees are required to take Mandatory Courses on an Annual Basis, however some employees are required to take not only mandatory courses but unique courses tied to their job description ie: Production Workers, Foundry Workers etc.

We track who has passed their due date "aka expired" and who has a course that is to be completed within 90 days

Each department has x number of employees and some of those employees within that department may be required to complete a course by X date.

How can I randomly select employees who require training within all departments,schedule a training date, however making sure that I don't pull everyone from the same department on the same schedule date. Pulling everyone from the same department for the same scheduled training date would leave the departments un-staffed.

There are 20 departments and 290 employees that all require training.

Any help would be appreciated.

Thank you

Gene

[Micron]

You'd have to indicate what the logic would be behind the selection. Those who's due date is closest? Those who've had little or no training? Those who have some sort of priority due to impending projects or tasks? What about those who have no vacation scheduled during the training session or might be on some sort of leave at that future time? Would you prioritize those who have not had the training over those who might be getting a recertification?
Not so cut and dried maybe? Or is it as simple as only setting a percentage target and randomly choosing from a department until you approach the max percentage? If that were the case, you'd have to decide on how to address .5 employees. Round up or down?

[hikerdood]

Understood, excellent questions and thanks for your response. We are not so much concerned about vacation as they can always be rescheduled. Here are my million dollar questions, however if I can give a little background.

We have an employee table with last name, first name and the primary key is their badge # along with other info. We have a training table with last name , first name , badge # and all 30 courses related to employee table by badge number. I created a query for all 30 courses that pulls records where courses are past due. I created a union query summarizing all 30 queries to give the Training Director a summary report of expired courses. I also created an individual query for each supervisor based on the union and employee table. We then created 30 course queries based on a 90 day look ahead for those employees where course completion is due within 90 days. We also created a 90 day union query and 90 day supervisor query. This gives the training director the ability to ask for summary and individual reports.

The headers for the most part are: Badge #, Lname, Fname, Job Desc, Depart Code, Supers Name, Course Name, Course Expiration date, etc etc.


Here are my questions:

#1. On the 90 day look ahead and expired queries, I don't have the brain power to create the logic that will pull a select
number of people from each department such that I don't pull everyone from that department. Not even sure which fields
the logic would be applied to.

#2. If I was able to determine the logic and run the first scheduled training/date class and then run another scheduled
training/date class right after, how can I prevent the query from pulling those same employees that may have been
selected in the 1st run.

#3. If the Training Director gives me dates that are opened for course scheduled training, how can I get those
dates into the query. Do I need to create a table that lists the courses and scheduled training dates and then create a
relationship between the courses in the scheduled training table and the Union 90 day Look Ahead and Expired courses
queries.

#4. If I create the scheduled training table and relate it to the Union "90 day Look Ahead and Expired" how can I insure
that it picks dates that are next in line and not the same dates that are in the 1st run.

Hope I am making myself clear.

Regards

Gene Haines

[Micron]

We have a training table with last name , first name , badge # and all 30 courses

I think there are 2 issues with this: you're repeating information already contained in and belonging to the employee and that table. Badge # (hopefully you didn't really name it that) is OK as a primary key field as long as that number will never be reused by anyone else and will never change. Others would lean heavily towards an autonumber field instead. However, names and numbers should not be in the training table, which brings me to point #2.
#2 Courses don't belong in the training table.

You got help here to create this db; by browsing or actually getting answers to posted questions? I don't see much history for your user name, and I see potential issues, if they are not already:
- Names should not have special characters or spaces
- you shouldn't need 30 queries because you have 30 courses (or any other thing)
- you shouldn't need a Union query to bring this 30 query information together
- you shouldn't need a query for each supervisor (ditto on the Union query for this)
- you appear to be managing decisions based on datasheet views of queries rather than forms and reports (not sure about that one)
- you don't have enough tables for a properly designed db, assuming there aren't others that you haven't mentioned (such as tblDepts)
I'm going to suggest you pause and review normalization and a few other things. Hopefully you've not been given this info before, otherwise I wonder why I think it necessary to offer it now. Look for some links at the bottom of this post.

#1. On the 90 day look ahead and expired queries, ... pull a select number of people from each department such that I don't pull everyone from that department. Not even sure which fields the logic would be applied to.

Not sure if you're just reporting on this or jumping right to scheduling. Assuming reporting (the foundation would be the same I think), how I think I'd approach this: Assuming you don't have multiple concurrent users in the same front end, have a temp table (tmpRoster) with all the pertinent fields related to compiling such a list - maybe course id & name, trainee, etc. In code, clear the table on form opening. You'd need a stored query (which gets the criteria from a form) as an Append to the temp table. The query starts out as a Select so you can tweak until it's right. It returns the Top n results (where n is a value in field MaxAllowed, tblDepts, which holds this value in txtMaxAllowed on your form (doesn't have to be a visible control). So the query returns the Top n employees for a given dept Where the ExpiryDate for a given course is <= Date-90. When this is correct, you change it to an Append query. Now you need a way to repeat the process for any or all depts. To me, two listboxes are good for this. lstFrom contains all the departments. Between lstFrom and lstTo are 4 small buttons positioned one above the other; 1 each with arrows - single left, single right, multiple left and multiple right. The first 2 move a selected list item to the other box. The second 2 move everything.There is a command button for running the query once for each list item in lstTo. When clicked, code appends to the temp table a set of records for each dept in lstTo listbox, then opens a report based on that table. There are other ways to be sure, but what comes to mind restricts you in that you can only run the query once for a selected dept in a combo box, or multiple times in a multi select combo. Not as elegant in either case, and woe is you if you goof up on the selections in a multi combo. You may have to reselect.

#2. If I was able to determine the logic and run the first scheduled training/date class and then run another scheduled
training/date class right after, how can I prevent the query from pulling those same employees that may have been
selected in the 1st run.

In conjuction with the above, some primary table (and maybe the temp table if you want to report on tentative scheduling) should have a DteScheduled field so you can create the attendee slots. A query (or maybe the query above) similar to the aforementioned would exclude anyone whose criteria didn't meet the requirements, which for this case would include DteScheduled being Null. If not Null, they are eliminated from subsequent scheduling.

#3. If the Training Director gives me dates that are opened for course scheduled training, how can I get those
dates into the query. Do I need to create a table that lists the courses and scheduled training dates and then create a
relationship between the courses in the scheduled training table and the Union 90 day Look Ahead and Expired courses
queries.

I think you are trying to do too much without forms. AFAIK, any query can get a criteria value from a form control.

#4. If I create the scheduled training table and relate it to the Union "90 day Look Ahead and Expired" how can I insure
that it picks dates that are next in line and not the same dates that are in the 1st run.

Thinking the above comments address this one also.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or http://holowczak.com/database-normalization/

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm