1 Reply Latest reply: Jun 11, 2013 2:41 PM by Tony Strano RSS

    Calculating 2 Tables with Multiple Many to Many Relationships

      I am perplexed.

       

      I have 2 data tables:

      • One that tracks shipments for Parts, Customers and by Quarter,
      • The other tracks returns for a Part, Customer by Quarter. Additionall there is a Point of Failure (POF) and Failure Mechanism (FM)

       

      The 2 tables are joined by these 3 fields

       

      I need to calculate the return rates in Parts per Million PPM = (Returned/Shipped)*1000000.

       

      Where I run into issues is calculating PPM for a given Customer or Part by POF and/or FM

       

      What want to get is

       

      CustomerPOFVolumeRetunredPPM
      Customer 1FIeld1,000,00011
      Customer 1Production1,000,00022
      Customer 10km1,000,0000

       

      What I get is:

       

      CustomerPOFVolumeRetunredPPM
      Customer 1FIeld1,000,00011
      Customer 1Production1,000,00022
      Customer 10km0

       

      I can not come up with a correct Set Analysis where by it will give be back the total volume for the customer unless there is a value for the POF. This is a simplified version and the acutal data, where I get differing values for Volume (which should reman constant) depending on the number of returns......

       

       

      Thank you fo any thoughts anyone might have..........