移调一组值的行列在Access 2010 [英] Transpose a set of values in rows to columns in Access 2010

问题描述

我有一个看起来像这样的Access数据库：

I have an Access database that looks something like this:

ID     |   TestDate   |  Test1  |  Test2  |  Test 3  |
1      |    Date1     |   10    |    20   |    25    |
1      |    Date2     |   8     |    21   |    23    |
1      |    Date3     |   9     |    18   |    23    |
2      |    Date1     |   13    |    19   |    22    |

我想行数据转列和维护previous列标题的名称，例如：

I wanted to transpose the row data to columns and maintain the name of the previous column headings, like so:

ID = 1
       |   Date1   |  Date2  |  Date3  |   etc...
Test1  |    10     |    8    |    3    |
Test2  |    20     |   21    |   18    |
Test3  |    25     |   23    |   23    |

ID = 2
       |   Date1   |
Test1  |    13     |
Test2  |    19     |
Test3  |    22     |


*The Date1 in the different IDs need not be the same. Date1 is the date the ID 
 had the Test for the first time. 

这样，它会更容易监视测试值的趋势。我试图寻找，但查询我偶然发现的附加价值。我只需要调换，而无需复制并粘贴到Excel中的数据。任何MS Access查询或VBA code是多少AP preciated。谢谢你。

This way it will be easier to monitor the trend of the test values. I tried looking but the queries I stumble upon add values. I just need the data transposed without having to copy and paste into excel. Any MS Access query or VBA code is much appreciated. Thank you.

推荐答案

怎么样：

TRANSFORM SUM(q.testval) AS sumoftestval
SELECT q.id,
       q.test
FROM   (SELECT t2.id,
               t2.testdate,
               "test1"  AS Test,
               t2.test1 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test2"  AS Test,
               t2.test2 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test3"  AS Test,
               t2.test3 AS TestVal
        FROM   t2) AS q
GROUP  BY q.id,
          q.test
PIVOT q.testdate; 

选择一个ID

TRANSFORM Sum(q.testval) AS sumoftestval
SELECT q.test
FROM (SELECT t2.id,
               t2.testdate,
               "test1"  AS Test,
               t2.test1 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test2"  AS Test,
               t2.test2 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test3"  AS Test,
               t2.test3 AS TestVal
        FROM   t2)  AS q
WHERE q.id=1
GROUP BY q.test
PIVOT q.testdate;

这篇关于移调一组值的行列在Access 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！