我试图在MS Access中创建一个查询,以最终从中获取输出:
Name Cat 1 Cat 2 Cat 3 Cat 4 Cat 5 Cat 6 Joe 2 12 10 1 0 0 Bob 0 0 0 0 0 0 Jody 2 4 3 1 2 0 Harry 0 4 14 0 2 0
对于这样的事情:
Name Joe Bob Jody Harry Cat 1 2 0 2 0 Cat 2 12 0 4 4 Cat 3 10 0 3 14
这有可能吗?
编辑
SELECT [Authorizer Name], Sum([Q1A - CD # 1]) AS [Category 1], Sum([Q2A- CD # 2A] + [Q8A- CD # 2A] + [Q10A- CD # 2A] + [CTS A accurate- CD # 2A] + [e-correspondence A accurate- CD # 2A]) AS [Category 2], Sum([Q7A- CD # 2B] + [Q9A- CD # 2B] + [Q11A- CD # 2B] + [CTS A procedures- CD # 2B] + [e-correspondence A procedures- CD # 2B]) AS [Category 3], Sum([Q4A- CD # 3]) AS [Category 4], Sum([Q5A- CD # 4]) AS [Category 5], Sum([Q12A- CD # 5]) AS [Category 6] FROM [Review Results] WHERE [Review Results].[Authorizer Name] = 1 GROUP BY [Review Results].[Authorizer Name];
该表当前是这样的:
Name X1 X2A X2B X2C X3A X3B X3C X4 X5 Joe 1 5 0 1 1 5 6 0 0 Bob 2 7 0 2 1 4 2 1 9 Billy 0 8 0 3 1 3 1 0 9
这就是我想要得到的:
Name Joe Bob Billy X1 1 2 0 X2 (sum of X2A/X2B/X2C) 6 9 11 X3 (sum of X3A/X3B/X3C) 12 7 5 X4 0 1 0 X5 0 9 9
首先创建一个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;