Presumably, you have a table of employees with one record for each employee. You'll also need a table of Training with one record for each training class that might be taken with a field for the hours needed for that class. Then you need a third table where you document the fact that an employee has taken or (at your option) been scheduled/assigned to take that class that links to the other two.
Start with these relationships:
Employees::__pkEmployeeID = Employee_Training::_fkEmployeeID
Training::__pkTrainingID = Employee_Training::_fkTrainingID
You can place a portal to Employee_Training on the Employees layout to list and select Training records for each given Employees record. Fields from Training can be included in the Portal to show additional info about each selected Training record and the _fkTrainingID field can be set up with a value list for selecting Training records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
An hours field in Employee_Training can be defined like this:
If ( IsEmpty ( Attendance ) ; 0 ; Training::HoursRequired )
and clear the "do not replace existing value..." check box
Anytime you modify the attendance field, this calculation will kick in and look up the hours for the specified training class.
Thanks for the reply. WOW, i'm not as savy with functions as you are. Actually, the database is set up to count each training event as a separate file since we also train people from outside the agency. So I can have a person listed multiple times for taking different trainings as nobody takes the same one twice.
So I have been noodling around with the CASE function and I seem to have a basic version set up that works partially. Here is the actual formula:
Case ( Training Topic="Wrap Basic" and Attendance Session 1="X"; 3)
That works for that single instance. but I have other trainings. I have discovered that all other trainings are 6 hours trainings. So it would seem all I need is to add something to that function to that if the "Training Topic" field does NOT equal "Wrap Basic", then it should set the hours field to "6". That would pretty much do it for all the functionality I would need for that. Is that possible?
What I have outlined should do exactly what you have requested. The system allows you to record training info on any number of employees and for any given employee, you can document as many training sessions/classes/whattever as needed for that one employee. There is no upper limit on the number of classes that you can link to any one employee.