I don't quite follow this part:
In order to create these different task relationships in the relationships graph, I had to duplicate the job costing table for each new task relationship... it would only let me relate the job cost table to the employee table once.
If I understand correctly, you should have ONE table for tasks (with 6 records?). The job costing table should be related to both - so for each record you would choose both the employee and the type of task performed.
I hadn't considered having a table for tasks... I currently have all the tasks in the job costing table. To clarify, each record (job) in the job costing table will require all 6 tasks. Each task may be completed by any of my employees, but each employee costs the company a slightly different rate. So, if I make a table for tasks with 6 records, I would relate job costing table to task table and to employee table as you suggest. Then, I would select the employee who did each task on a given job in job costing table. That would then allow me to correctly calculate the actual labor cost for each task in the job. Is this how you see it working?
I still don't understand this:
To clarify, each record (job) in the job costing table will require all 6 tasks.
A record in the job costing table is not the job - it's a component of a job, and it should relate to a specific employee doing a specific task (for a specific job). There should be another table listing the Jobs.
Normally, the Tasks table would be also related to Jobs (one job has many tasks). It seems you are saying every one of your jobs has a permanent set of 6 tasks?
Of course, being new, I may not have the theory correct. But I built the job costing table as one record = one job. I am painting and installing doors, so each job will have multiple tasks:
- check the incoming door for correct measurements
- prep the door
- paint the door
- load the door(s) and pull the hardware out of inventor for delivery
- deliver the door
- install the door
ALL jobs will have all tasks. Currently it works if all tasks are performed by the same person, but not if there are multiple employees involved. I believe that's because the job costing table is relating to one record in the employee table, and if another employee is chosen for one or more tasks, it returns a null response, and hence a zero in the cost for that task.
How do I change the design to get the desired information?
Thanks for your input.
I believe you should have 4 tables: Jobs, Tasks, JobTasks and Employees.
The Jobs table contains JobID and fields that describe the job (customer, date, etc) but no fields that describe the tasks or employees.
The Tasks table holds 6 permanent records for the 6 tasks you have described.
The JobTasks table has fields for:
• EmployeeRate (lookup from Employees)
• cCost = Hours * EmployeeRate
The relationships required:
1. Jobs::JobID = JobTasks::JobID
2. Tasks::TaskID = JobTasks::TaskID
3. Employees::EmployeeID = JobTasks::EmployeeID
For each new job, import the 6 records from Tasks into JobTasks (mapping TaskID to TaskID) and set the JobID value of the imported records to the JobID of the newly created job record (this should be all scripted together under Create New Job).*
Place a 6-rows portal to JobTasks on a layout of Jobs. In this portal, you will select the employee for each one of the 6 tasks.
(*) There is another method that can create the 6 tasks records automatically as needed - but it's somewhat complex and I wouldn't be able to describe it in a forum post.
I'll digest this and give it a try. Will let you know if I have other questions... thanks.