title: MS Access - multiple TRANSFORM query?
body: <p>So I have the following table:</p>
  
  <pre><code>Record ID       Test ID Test Type       Value   Unit    Passed Test
  1               1       Visual                              1
  2               1       Earth Current   25          A       3
  3               1       Earth           0.07        Ohm     1
  4               1       Insulation      &gt;99.99      MOhm    1
  5               1       Leakage         &lt;0.1        mA      1
  6               1       Touch Leakage   &lt;0.1        mA      1
  7               1       Load            &lt;0.05       kVA     3
  8               2       Visual                              1
  9               2       Earth Current   25          A       3
  10              2       Earth           0.07        Ohm     1
  11              2       Insulation      &gt;99.99      MOhm    1
  12              2       Leakage         &lt;0.1        mA      1
  13              2       Load            &lt;0.05       kVA     3
  14              2       Polarity                            1
  15              3       Visual                              1
  16              3       Earth Current   25          A       3
  17              3       Earth           0.16        Ohm     1
  18              3       Insulation      &gt;99.99      MOhm    1
  19              3       Leakage         &lt;0.1        mA      1
  20              3       Load            &lt;0.05       kVA     3
  21              3       Polarity                            1
  </code></pre>
  
  <p>What I want to do is select (with a few joins to other tables) from this, while using TRANSFORM to make new columns using Test Type as headers. That much I've managed, but the problem comes with the Polarity and Visual Test Types; basically for the other Test Types the value I select needs to be from the Value column, but for Polarity and Visual it needs to be from the Passed Test column.</p>
  
  <p>Then I also need to concatenate the Unit in with the values, and replace "1" or "3" with "PASS" and anything else with "FAIL" for Polarity and Visual.</p>
  
  <p>So I need to end up with something like the following:</p>
  
  <pre><code>Earth      Earth Current   Insulation      Leakage    Load       Polarity           Touch Leakage   Visual
                             &gt;99.99 MOhm     &lt;0.1 mA    &lt;0.05 KVA  PASS                               PASS
  &lt;0.04 Ohm  25A             &gt;99.99 MOhm     &lt;0.1 mA    &lt;0.05 KVA  PASS                               PASS
  0.07  Ohm  25A             &gt;99.99 MOhm     0.31 mA    &lt;0.05 KVA  PASS               &lt;0.1            PASS
  </code></pre>
  
  <p>Here's what I have so far:</p>
  
  <pre><code>TRANSFORM First([Value]) AS Result
  
  SELECT DISTINCT
      Assets.Make,
      Assets.Model,
      Assets.[Asset ID],
      [Asset Tests].[Test Date],
      [Asset Tests].[Passed Test]
  
  
  FROM 
      (Assets 
  INNER JOIN
      [Asset Tests]
  ON
      [Asset Tests].[Asset ID] = Assets.[Asset Key])
  INNER JOIN
      [Asset Test Records]
  ON  
      [Asset Test Records].[Test ID] = [Asset Tests].[Test ID]
  
  GROUP BY
      [Asset Test Records].[Test ID],
      Assets.Make,
      Assets.Model,
      Assets.[Asset ID],
      [Asset Tests].[Test Date],
      [Asset Tests].[Passed Test]
  ORDER BY 
      Assets.[Asset ID]
  PIVOT 
      [Test Type]
  </code></pre>
  
  <p>It looks to me like I might need some sort of multiple transform thing going on...but I have no idea where to start with that. Or am I barking up the wrong tree?</p>
  
  <p>Thanks</p>
  
  <p>EDIT: I should add that I have zero control over the input, otherwise this whole thing would be much easier. The database file comes in like that and I have to deal with it.</p>