Relationships and lookup of 4 tables

[dijilator]

I have the following tables with unique IDs and Column Values corresponding to the table



Employee table: EmployeeID, EmployeeName
Task table: TaskID, Task
Subtask table: SubtaskID, Subtask
Training table: TrainingID, EmployeeID, TaskID, SubtaskID, TrainingDate

The training date could be used as the marker of whether the employee was trained, or it could be a separate field associated with the training. I prefer the latter.

For the life of me, I can't figure out how to relate, lookup, and query the tables to get a form that has

Lookup employee by EmployeeID

Display a subform that shows employees current training
Allows me to select Task, then subtask, to add training.

I can get pieces, but am having a whole lot of trouble making it all work together. Some suggest lookups in the tables, but don't show how to filter it in a table when referencing the tasks and subtasks in a training table.. Others suggest combo boxes, but don't show how to quickly add 40 tasks and subtasks to an employee's training on one form, as a subform would do.

[June7]

Main form bound to Employee table, subform bound to Training table. Comboboxes in subform to select task and subtask.

However, if there can be multiple subtasks for each task for each employee, might need another table and a subsubform.

tblEmployees
EmpID

tblTasks
TaskID
Task

SubTask
SubTaskID
TaskID
SubTask

tblTrainingTasks
TngID
EmpID
TaskID

tblTaskSubtasks
TngID
SubTaskID

[ssanfu]

Looks OK to me... except I would have fields:
Employee table: EmployeeID, LastName, FirstName

(and no spaces in object names!)


Subform would have query with all fields from Training table. Subform in continous forms view. Combo boxes for TaskID, SubtaskID, and a text box for TrainingDate.


For TaskID, control source would be the FK (TaskID), Row source would be query for Task table.
Other FK fields the same setup.

Main form either have a query from Employee Table (continous forms view) or combo box to select employee. Main form/Child form linked on EmployeeID.

[dijilator]

Thank you June7 and ssanfu!

What I ended up doing is making a junction table for the Tasks and SubTasks using the primary key from each and then making those two a composite key. Then in the training table, I have the following columns:

EmpID; TaskID, SubTaskID (as a composite key); and TrainingDate.

I've uploaded the database in a zip file so that you can see how the tables are related, etc.

A few questions:
How can I have the Task and SubTask names show up in the Training table instead of their ID numbers?
(I considered using the Task names as their own primary keys, since they will always be unique, but I don't think that is conventional database design.)

In the training table, why are there multiple instances of the same subtask for a given task?
I'm sure it has to do with a relationship.

[June7]

I avoid compound keys whenever possible, although two fields is not so bad. I have one table that would have required 4 fields for a unique identifier. I use an autonumber field in that table as the PK. To prevent duplicate combinations of the fields, set a compound index - does not have to also be set as primary key.

[ssanfu]

How can I have the Task and SubTask names show up in the Training table instead of their ID numbers?

You don't... or at least you shouldn't. My first suggestion would be to remove the look up fields. You want to see task and subtask names, use a query.
My view is that tables are only for storing data: NO look up fields, NO MVFs, NO calculated fields.

In the training table, why are there multiple instances of the same subtask for a given task?

Given that you are using a compound key, there can be only 1 combination of Task and Subtask.



Currently you have 4 employees. If each employee is required to have the same combination of Task and Subtask training, there can only be 1 entry in the Training table with that combination. Remember that Task & Subtask are used in a compound primary key and therefore must be unique.

So it depends on the training requirements.
If John Brown required to have training on Task 2 (sales) and Subtask 3 (Changes) only once, then set an index on EmpID, TaskID and SubtaskID.
If John Brown is required to have training for Task 2 and Subtask 3 once a year, then the index should be on EmpID, TaskID and SubtaskID and date.

It would still be possible to have the same training on multiple days, so you would still need to ensure that his training was only once per year.

Not knowing anything about your requirements, this is how I would start designing the structure and relationships.

Again, this is just my opinion....

[dijilator]

What I was hoping for in the end was something that would make training updates quick for the end user (supervisors). We have about 40 jobs and 5-20 stages for each job. The jobs are all unique, but often the stages are the same (in all, there are maybe 50 unique stages). Some jobs have all of the stages, some of them have only some of the stages. Each supervisor has about 25 employees to update training on. So here is a list of wishes:

1. Update employee with multiple jobs and stages in one screen (I'm thinking subform).
2. When the supervisor selects the Job, the next column would give a lookup of only the stages associated with that job.
3. Perhaps after selecting the employee in a combo box, the supervisor could select the job in a subform combo box and then the subform would list only those stages that pertain to the selected job.

The reason I want to filter by job is that the list of about 50 stages could get rather tedious to go through.

I think #3 would be the ideal answer, but I'm back to figuring out how to relate those job/stage or task/subtask tables.

Thank you for the database suggestion!

[dijilator]

I'll have to study compound indices. I'm sure many articles are on the net. Any you already have in mind to suggest?

[June7]

Top return in Bing search: http://www.geeksengine.com/article/c...ex-access.html

[dijilator]

You don't... or at least you shouldn't. My first suggestion would be to remove the look up fields. You want to see task and subtask names, use a query.
My view is that tables are only for storing data: NO look up fields, NO MVFs, NO calculated fields.

I'm probably overlooking something very simple, but would you please explain how to associate a task and subtask from two different tables through a query when one is not associated with the other via lookup? My common method is to put a task lookup on the same row of the subtask so that when viewing the task table, the subtask can be seen as a subtable. I've moved on from the original scenario, but would like to know this part about the query for current and future use.

[orange]

I suggest you sort out where all the tables in your database fit within a data model.


Seems a Task could have 0,1 or Many SubTasks

I see Jobs and Stages in your description. I think you have to clarify, in plain English, What you are trying to accomplish.

Your database should support your "business" and it seems that business has not been adequately described (to me at least).