CODEWITHSUNDEEP
sqlms-access
Jez Clark
Jez Clark

Reputation: 383

MS Access - multiple TRANSFORM query?

So I have the following table:

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      >99.99      MOhm    1
5               1       Leakage         <0.1        mA      1
6               1       Touch Leakage   <0.1        mA      1
7               1       Load            <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      >99.99      MOhm    1
12              2       Leakage         <0.1        mA      1
13              2       Load            <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      >99.99      MOhm    1
19              3       Leakage         <0.1        mA      1
20              3       Load            <0.05       kVA     3
21              3       Polarity                            1

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.

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.

So I need to end up with something like the following:

Earth      Earth Current   Insulation      Leakage    Load       Polarity           Touch Leakage   Visual
                           >99.99 MOhm     <0.1 mA    <0.05 KVA  PASS                               PASS
<0.04 Ohm  25A             >99.99 MOhm     <0.1 mA    <0.05 KVA  PASS                               PASS
0.07  Ohm  25A             >99.99 MOhm     0.31 mA    <0.05 KVA  PASS               <0.1            PASS

Here's what I have so far:

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]

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?

Thanks

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.

Upvotes: 0

Views: 3392

Answers (1)

Matt Weller
Matt Weller

Reputation: 2754

EDIT: This can be done in a single step, the answer I gave below is long-winded, sorry.

TRANSFORM First(IIf([test_type]="visual" Or [test_type]="polarity",[test_result_text],[test_value] & " " & [unit])) AS output_value
SELECT tbl_Test.Test_ID
FROM tbl_Test INNER JOIN lk_test_result ON tbl_Test.Test_Result = lk_test_result.test_result
GROUP BY tbl_Test.Test_ID
PIVOT tbl_Test.Test_Type;

The q_test_basis in my original solution can be made into a crosstab itself without needing a second query to sit on top of it. Apologies for the confusion!

=== ORIGINAL SOLUTION IN 2 STAGES ===

I imported your data and created the queries you need. Please note that the field names are not identical but this describes both the logic and the required formula.

Base Query to make a single column of output values using the logic you defined:

SELECT tbl_Test.Record_ID, tbl_Test.Test_ID, tbl_Test.Test_Type, IIf([test_type]="visual" Or [test_type]="polarity",[test_result_text],[test_value] & " " & [unit]) AS output_value
FROM tbl_Test INNER JOIN lk_test_result ON tbl_Test.Test_Result = lk_test_result.test_result;

This produces an output like this:

The base query q_test_basis is then used as the input for the crosstab query, defined in the standard way. 

TRANSFORM First(q_test_basis.output_value) AS FirstOfoutput_value
SELECT q_test_basis.Test_ID
FROM q_test_basis
GROUP BY q_test_basis.Test_ID
PIVOT q_test_basis.Test_Type;

Note that the columns will be sorted alphabetically on test_type. I can paste the screenshots for the query design also if you require, but I think it was only the 2-stage approach and the conditional field that were missing from your solution.

Upvotes: 1

Related Questions