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
      123456XYZ10.05.28.32

       

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

       

      Transaction Table:

      Part NumberWork OrderQty Completed
      123456XYZ10

       

      Cost Table:

      Part NumberCost_IDStd. Cost
      1234561.05
      1234562.28
      1234563.32

       

      Cost_ID Table:

      Cost_IDCost_Description
      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!