vba 将行中的一组值转置为 Access 2010 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11561422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transpose a set of values in rows to columns in Access 2010
提问by ome
I have an Access database that looks something like this:
我有一个看起来像这样的 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 |
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.
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.
这样可以更容易地监控测试值的趋势。我试着寻找，但我偶然发现的查询增加了价值。我只需要转置数据，而不必复制并粘贴到 excel 中。非常感谢任何 MS Access 查询或 VBA 代码。谢谢你。
采纳答案by Fionnuala
How about:
怎么样：
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;
Select an 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;
