访问联合/枢轴以交换列和行 [英] Access Union/Pivot to Swap Columns and Rows

问题描述

在此处访问 2010.我有一个问题(谢谢 Andomar！):

Access 2010 here. I have a query (Thank you Andomar!):

SELECT Inspection.Date, count(*) AS [# Insp], sum(iif(Disposition = 'PASS',1,0)) AS [# Passed], sum(iif(Disposition = 'FAIL',1,0)) AS [# Failed], sum(iif(Disposition = 'PASS',1,0)) /  count(*) AS [% Acceptance]
FROM Inspection
WHERE Disposition in ('PASS', 'FAIL') AND ((Inspection.Date) Between Date() And Date()-30)
GROUP BY Date;

这给出了一个这样的表格:

That gives a table like this:

Date       | # Insp | # Passed | # Failed | % Acceptance
11/26/2012 | 7      | 5        | 2        | 71 
11/27/2012 | 8      | 4        | 4        | 50 
...

我希望使用此查询为图表下方的子表单制作表格"，仅供参考.表格"的格式很重要，因为它需要列(日期)和行标题.括号里有表格，强调表格是实时生成的；换句话说，不存储为 Access 对象.

I am looking to use this query to make a "table" for a sub-form that will be below a graph, for reference only. The formatting of the "table" is of importance, as it needs both Column (Date) and Row headings. I have table in parentheis to emphasize that the table is generated in real time; in other words, not stored as an Access object.

最终的结果会是这样的:

The end result will be someting like this:

Date         | 11/26/2012 | 11/27/2012 ...
# Insp       | 7          | 8
# Passed     | 5          | 4
# Failed     | 2          | 4
% Acceptance | 71         | 50

这似乎是一个最佳情况，因为轴刚刚翻转，但对于我的生活，我找不到不破坏数据的解决方案.交叉表查询仅让我针对单个值过滤一个或多个类别.这是一个工会的用途吗?还是一个支点?需要转型吗?看起来应该是这么简单的问题.这是可以在 SQL 中完成的事情，还是需要 VBA 来安排表格"?感谢您的帮助！

It seems to be an optimal case, as the axis are just flipped, but for the life of me, I cannot find a solution that does not destroy the data. A Crosstab Query only gave me filtering on one or more categories against a single value. Is this something a union would be used for; or a pivot? Would a transform be needed? It seems like it should be such a simple problem. Is this something that can be done in SQL or would VBA be needed to arrange the "table?" Thanks for the help!

这些链接似乎确实适用:

These links do seem applicable:

MS Access 中的列到行

如何将行转为列

推荐答案

这必须是一个两步转换的过程.首先，您必须将当前查询中的数据旋转为行而不是列，然后您必须将日期转换为列而不是行.

This will have to be a two-step process to transform. First you will have to rotate the data in your current query to be in rows instead of columns, then you will have to transform the dates into columns instead of rows.

查询将是这样的:

TRANSFORM max(val) as MaxValue
SELECT col
FROM
(
  SELECT [Date], '# Insp' as Col, [# Insp] as  val 
  FROM yourQuery
  UNION ALL
  SELECT [Date], '# Passed' as Col, [# Passed] as val
  FROM yourQuery
  UNION ALL
  SELECT [Date], '# Failed' as Col, [# Failed] as val
  FROM yourQuery
  UNION ALL
  SELECT [Date], '% Acceptance' as Col, [% Acceptance] as val
  FROM yourQuery
) 
GROUP BY col
PIVOT [Date]

我猜您当前的查询已保存在您的数据库中，您将在我的示例中将 yourQuery 替换为您的查询名称.

I am guessing the your current query is saved in your database, you will replace the yourQuery in my example with the name of your query.

我刚刚在 MS Access 2003 中使用您上面示例中的值对此进行了测试，它产生了您想要的结果.

I just tested this in MS Access 2003 with the values in your sample above and it produced the result you want.

这篇关于访问联合/枢轴以交换列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！