Adding new variable to existing data. Add new table?

[Harley Guy]

Thanks in advance to those who have helped me in the past. I can see that while I can envision what I want a database to do, I don't have all the tools to make it so. This is a great forum and people here are very willing to help and I just want to express my gratitude...



Ok...enough sunshine. On to my issue,

I have a database that shows what person can do what task, what their competency level is and the last date they were within compliance of performing that task. Thought I was done with it but I have had a new wrinkle thrown at me. There has been a requirment that each task go under a job name. If you are not familiar with job leveling, it has to do with how many tasks are within that job. So if I have one job that has 8 tasks and another job that has 4 and both jobs have to be performed within 30 minutes, I can view if one job is over 30 minutes or not. If so, I can move one task from a job to another to level the job.

So what I need to be able to do is to have a job number and job name table and be able to attach task name and numbers to the job name:

Job: 2101 Cab Exterior Options
Task: 21-01 Grab Handles
21-02 Slide Bar
21-03 Floodlights
21-04 Beacon Lights

I can create the table for the Jobs but how do I set up the relation from job to task?

Thanks!

[Dal Jeanis]

Okay, let me make sure I understand.

You have a table of Tasks - the generic "Task types" that people might know how to do.

You also have actual work to be performed and tracked.

Now, is it the generic tasks going to be grouped into generic jobs, or is it the actual work that is going to be split up into "Jobs"?

In other words, is your job leveling going to occur in advance at the theoretical level, or the practical level when a specific person gets assigned the tasks?

Either way, you'll have a Job record that has the overall job information, and then you'll have a junction table that links that job number to the particular task numbers to be performed as part of that job. The difference comes later, in how you assign the job to a worker.

[Harley Guy]

Jobs would be considred generic while tasks would be actual work. We would group the number of tasks in each job. Job leveling will be at the practical level. I would think that the task record would be the driver instead of the job record since that's how the current database is being driven off of.

What I need to be able to do is create a job and list the tasks under it...does that make sense?

[Dal Jeanis]

Good enough.

You probably already have a table that lists the generic kinds of tasks that can be selected between (tblTaskType). Hopefully, this table has a unique key for each kind of task. (taskTypeID)

You probably already have a table that has the individual practical tasks that are now being assigned and done (tblTasks). Hopefully, this table already has a unique key for that particular task (taskID), and a foreign key to what kind of task it is on the First table (taskTypeID).

Create a table for the Job (tblJobs). Give it a unique key for each job (JobID).

Create a junction table (tblJobTasks) to show what tasks are connected to what jobs. The only fields on this table are the foreign keys of the job (JobID) and the task (taskID).

That's all the structure you need.

[Harley Guy]

Dal, is there any chance you can send me an example? I've never used a junction table before...

I do have tables for tasks and jobs both with unique keys....I do not have a table for task type...not really sure I need one in this instance.

Thanks!

[Dal Jeanis]

Does someone actually type in "21-03 Floodlights"? Or is it selected from a dropdown box? The list that's behind the dropdownbox code could be stored in a table, and the table would become the source of the dropdown. That's the task type table I was talking about.

Make a new table. Give it two fields, the keys to the two tables. That's it. It's literally that simple.

If you wanted to store other information about that relationship, notes, or whatever, you add them to the record, and you make sure the junction record has a unique key of its own.

Suppose you had a table of owners and a table of pets. You might create a junction table called tblPetOwners to relate who owned which pet.

tblPetOwners 
   OwnerID
   PetID

That's all that's absolutely necessary to say who owns what. On the other hand, if you wanted to track adoption date and other deatils about the relationship, then I recommend the record get a unique id:

tblPetOwners 
   PetownerPK    Autokey
   OwnerID
   PetID
   AdoptionDate

If you want to list the owners and the pets, you join the three tables

SELECT 
   tblOwners.OwnerName,
   tblPets.PetName,
   tblPetOwners.AdoptionDate
FROM
  (tblOwners 
   INNER JOIN 
   tblPetOwners 
   ON tblOwners.OwnerID = tblPetOwners.OwnerID)
   INNER JOIN 
   tlbPets
   ON tblPets.PetID = tblPetOwners.PetID;