在多个字段上透视并从 Access 导出 [英] Pivot on multiple fields and export from Access

问题描述

我为一家制造工厂构建了一个访问应用程序，并向他们提供了一份报告，其中列出了流程中的不同数据点.我有一种方法可以生成如下所示的报告.

I have built an access application for a manufacturing plant and have provided them with a report that lists different data points along a process. I have a way to generate a report that looks like the following.

 Batch     Zone    Value1     Value 2   etc.
 25        1       5          15
 25        2       12         31
 26        1       6          14 
 26        2       10         32

但是，需要以不同的格式查看数据.他们希望每批一行，所有数据都是水平的.像这样...

However, there is demand to view the data in a different format. They would like one line per batch, with all data horizontal. Like this...

                Zone 1                Zone 2
 Batch     Value1     Value2     Value1     Value2
 25        5          15         12         31
 26        6          14         10         32

如果按照第二个示例显示，总共将有 157 列.有 7 个唯一的字段名称，但其余的是 14 种重复的不同数据类型.我无法查询以他们想要的格式显示数据，因为字段名称相同，但第一种方法并不难.我可以使用 VBA 将数据插入表中，但我不能使用重复的字段名称，因此当我将其导出到 Excel 时，字段名称将没有任何意义，并且不能有部分(如 zone1、zone2 等)我可以将报告链接到此，但报告宽度只能为 22"，因此我必须导出，然后对另一端的 Excel 表进行一些 vba 处理，以清晰易读的方式显示.

In all there will be 157 columns, if displayed as in the second example. There are 7 unique field names, but the rest are 14 different data types that are repeated. I can't get a query to display the data in the format the they want, do to the fact that the field names are the same, but it is not hard to do it the first way. I can use VBA to insert the data into a table, but I can't use duplicate field names, so when I go to export this to Excel the field names won't mean anything, and there can't be sections (like zone1, zone2, etc.) I can link a report to this, but the report width can only be 22", so I would have to export and then do some vba handling of the excel sheet on the other end to display in a legible way.

我可以将数据转换为#1 格式，有什么方法可以让数据根据批次号显示在一长行中?有没有其他人知道这是如何可行的?

I can get the data into format #1, is there some way I can get the data to display in one long row based on batch number? Does anyone else have a great idea of how this is doable?

欢迎任何建议.谢谢！

推荐答案

在你的问题中你说

我有一种方法可以生成如下所示的报告

I have a way to generate a report that looks like the following

然后将数据列为

Batch  Zone  Value1  Value2
-----  ----  ------  ------
   25     1       5      15
   25     2      12      31
   26     1       6      14
   26     2      10      32

现在也许数据可能已经在某处处于非透视"形式(在不同的行中具有不同的 Value )，但如果不是，那么您将使用类似于以下查询的内容来实现这一点

Now perhaps the data may already be in "un-pivoted" form somewhere (with different Values in separate rows), but if not then you would use something like the following query to achieve that

SELECT 
    [Batch],
    "Zone" & [Zone] & "_" & "Value1" AS [ValueID],
    [Value1] AS [ValueValue]
FROM BatchDataByZone
UNION ALL
SELECT 
    [Batch],
    "Zone" & [Zone] & "_" & "Value2" AS [ValueID],
    [Value2] AS [ValueValue]
FROM BatchDataByZone

...返回:

Batch  ValueID       ValueValue
-----  ------------  ----------
   25  Zone1_Value1           5
   25  Zone2_Value1          12
   26  Zone1_Value1           6
   26  Zone2_Value1          10
   25  Zone1_Value2          15
   25  Zone2_Value2          31
   26  Zone1_Value2          14
   26  Zone2_Value2          32

无论如何，如果您将该查询保存为 [BatchDataUnpivoted]，那么您可以使用一个简单的交叉表查询来串出"每个批次的值...

However you get to that point, if you save that query as [BatchDataUnpivoted] then you could use a simple Crosstab Query to "string out" the values for each batch...

TRANSFORM Sum(BatchDataUnpivoted.[ValueValue]) AS SumOfValueValue
SELECT BatchDataUnpivoted.[Batch]
FROM BatchDataUnpivoted
GROUP BY BatchDataUnpivoted.[Batch]
PIVOT BatchDataUnpivoted.[ValueID];

...返回...

Batch  Zone1_Value1  Zone1_Value2  Zone2_Value1  Zone2_Value2
-----  ------------  ------------  ------------  ------------
   25             5            15            12            31
   26             6            14            10            32

这篇关于在多个字段上透视并从 Access 导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！