11 Replies Latest reply: Apr 25, 2014 9:45 AM by Joe Hutchings RSS

    Joining multiple tables, and then converting rows to columns

    Joe Hutchings

      Hello --


      I have a project to create a production variance dashboard. I'm in the initial stages of trying to gather all of my data.


      One of the key aspects to this dashboard is it needs to display a straight table that shows the work orders completed on a day, the qty completed, and break out the material, labor, and overhead costs to then calculate some variances.


      They wish to see this table like so:

      Part NoWork OrderQty CompletedStd. Material CostStd. Labor CostStd. Overhead Cost


      The trouble I am having is that my data exists in 3 tables as follows:


      Transaction Table:

      Part NumberWork OrderQty Completed


      Cost Table:

      Part NumberCost_IDStd. Cost


      Cost_ID Table:

      1Std. Material Cost
      2Std. Labor Cost
      3Std. Overhead Cost


      How do I join these various tables together, while converting the "rows" of the cost descriptions into columns in order to display the values the way they wish? Further more, I'll then need to utilize the various costs to do further calculations.


      I appreciate your help!