Look up a field name by it's value then changing a corresponding field value

[DrMann]

I am creating a form that will allow an employee to track the amount of time spent on a specific job. I may be over thinking it because I’ve thoroughly confused myself.



Here’s what I have:
A table (Orders) containing information for multiple Jobs (primary key field - Job Number). Each Job has up to six sub-jobs (field - Job 1, Job 2, etc.). Each sub-job has the amount of processing time (field – Job 1 Hours, Job 2 Hours, etc.).
I want to create a form where the employee can enter the sub-job number with the start/stop time. The database would then search the table for the record and update the appropriate hour’s field.
Ex.
Sub-Job – 12345.3
Start Time – 2:15PM
Stop Time – 4:15PM

Sub-Job “12345.3” is in the “Job 2” field of Job Number “12345” so “Job 2 Hours” field would get updated to add 2.00 to the existing value.
The code would first have to locate which field the sub-job was in then update the corresponding hours field.

Any help would be appreciated.

[orange]

Perhaps if you consider an Employee works on a Job which can be broken down into Tasks. The Employee records Time worked against tasks. If Many Employees can work on the same Task, you need a way to record EmployeeWorkedHoursOnTask

Job--->Task--->EmployeeWorkedHoursOnTask<----Employee

tblJob
JobId
JobName
other Job info

TblTask
TaskId
JobIdFK
TaskName
other TaskInfo

tblEmployeeWorkedHoursOnTask
EmpIDFK
TaskIDFK
WorkDate
StartTime 'can determine Hours worked from StartTime and EndTime
EndTime
Other info

tblEmployee
EmpID
EmpFName
EmpLName
other EmpInfo

Just some thoughts for consideration based on your post.

Good luck.