移调一组值的行的列在Access 2010

Question

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

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 | 

我想行数据转列和维持前一列的名称标题如下：

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代码非常感谢。谢谢。

Answer 1

如何：

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;