首先创建一个UNION查询以规范化您的表,然后创建一个交叉表以显示数据:
SELECT [Name], "Cat 1" As Cat, [Cat 1] As CatVal FROM Table
UNION ALL
SELECT [Name], "Cat 2" As Cat, [Cat 2] As CatVal FROM Table
<...>
查询向导将指导您创建交叉表。
编辑评论
要规范样本数据表,您可以说:
SELECT [Name], "X1" As Cat, [X1] As CatVal FROM Table
UNION ALL
SELECT [Name], "X2" As Cat, Nz([X2A],0)+Nz([X2B],0)+Nz([X2C],0) As CatVal
FROM Table
UNION ALL
SELECT [Name], "X3" As Cat, Nz([X3A],0)+Nz([X3B],0)+Nz([X3C],0) As CatVal
FROM Table
UNION ALL
SELECT [Name], "X4" As Cat, [X4] As CatVal FROM Table
UNION ALL
SELECT [Name], "X5" As Cat, [X5] As CatVal FROM Table
假设联合查询是QueryX:
TRANSFORM First(QueryX.CatVal) AS FirstOfCatVal
SELECT QueryX.Cat
FROM QueryX
GROUP BY QueryX.Cat
PIVOT QueryX.Name;