My base tables are set up by department, columns are tasks for that department and the data rows contain employee name, training hours and training date for each task.
I need a view that combines these department tables (that contain different number of tasks per dept) grouped by task and/or employee. Resulting DataSet needed is shown at the below these 2 base table examples:
BASE TABLE 1 - Engineering (3 tasks are DoorOrders, JobPrep & JobEng)
ID DoorOrders JobPrep JobEng Employee Date
01 5 null 3 Bill 1/1/13
02 null 8 null Bob 1/1/13
03 8 null null Bill 1/2/13
BASE TABLE 2 - Milling (2 tasks are SheetGoods & Nailers)
ID SheetGoods Nailers Employee Date
01 3 5 Jen 1/1/13
02 null 8 Mary 1/1/13
03 8 null Jill 1/2/13
RESULTING VIEW - (Combining these 2 departments)
TaskName Employee Hours Date
DoorOrders Bill 5 1/1/13
JobEng Bill 3 1/1/13
JobPrep Bob 8 1/1/13
DoorOrders Bill 8 1/2/13
SheetGoods Jen 3 1/1/13
Nailers Jen 5 1/1/13
Nailers Mary 8 1/1/13
SheetGoods Jill 8 1/2/13
As you can see, I need to take each 'cell value' per employee per date per task and split it out in a vertical table setup.
I need a view, nothing editable, but something that I can expand as we add more departments and/or tasks per department.