Complicated form problem...I don't think it is even possible!

[thekruser]

I am really hoping this is possible, but I am not so sure. I am hoping you can figure this one out, because I am baffled.

What I have:
The attached db.

The problem:
While what I have does work, my boss is asking for a simpler method of inputting training for employees who transfer from other branches. Our corporate training schedules are not in line with one another. When an employee transfers to my branch, we have to add them to the database and input the training they have already received. The issue is, for each employee we are having to create a new class just for them, going through the entire "Add a Class" process and adding a class number to a course that already exists. My boss says that is too tedious.

For example:
My Branch: BranchA
Other brach: BranchB

BranchB conducts Sexual Harassment in the Workplace (Class number 3 (being the 3rd iteration of this class this year)) from 4 to 5 November. John attends this class. Then John transfers to BranchA.

BranchA conducted Sexual Harassment in the Workplace (Class number 3) from 2 to 3 November. When John presents his certificate to HR, they attempt to add his class information, which they cannot find so they have to create the class in the database. However, John's certificate only has a completion date.



My boss does not like how many steps it takes to add John to the database. I somewhat agree. It is easy enough with one course, but when we transfer 10-20 employees a month and each employee has between 10 and 30 different certificates, this becomes a daunting task.

What I am trying to do:
I would like to find a way to cut down on the steps to add information on employees who transfer to my branch. Here is my vision: one form with cboEmployeeID and sbfTraining. SbfTraining (datasheet view) should have cboCourse, cboClass, txtFromDate, txtToDate, and a checkbox for 'Certificate Received at HR'.

This magical (or perhaps mythical) form should create a record in tblTrainingParticipants and allow for entering a date that does not exist in tblTrainingEvolutions.

Any ideas? Can you think of a better way for me to arrange the tbls (that would be a HUGE pain to change at this point, but I want to do this right). Many statistical reports rely upon the dates an employee completes a course.

If I am not being clear enough in my description of the problem, please let me know and I will try to clarify.

Thank you in advance for your help!!

[NTC]

I think part of the issue is conceptual in that you are viewing there only to be a 'class' when there is actually a 'Topic' vs 'Class'. The employee has completed the Topic, but not the specific Class that your branch offers.

Consider the university that offers topic Math 101, but there may be dozens of classes at different times being offered on different days and with differing dates. I think if you simply record the Topic and graduation year...perhaps the Branch location...that would suffice. It would be easy to copy your Class table to make a Topic table if need be.

if everyone is coming from Branch B - it could really save time if you could get B to export the training info to an excel or csv file.....setting up an import could be done pretty easily....you could have the employee bring it with them or email it to you.....

hope this helps.

[jzwp11]

If the training is offered at more than one branch, that describes a one-to-many relationship which requires a different table structure then what you have currently. If you restructure, you could ask the other branches for their training schedule once and upload those into the new structure ahead of time. Then when an employee transfers in, you will just need to link them to the specific session at the branch where they accomplished the training. With respect to the employee having only a completion date, it sounds like you need some new business rules that require the other branches to provide the actual scores as well as any other details you need Perhaps your boss can make that happen? Ideally, can't the other branches, just provide a spreadsheet of the data for tranferring employees such that you can just upload their entire training history it into your database? This might take more work up front, but should streamline things in the long run.

This sounds like an application where a common data system accessible to all braches would be most beneficial, but of course, arriving at one common way of doing things will have to be settled on beforehand.