我已经建立了制造工厂的访问应用程序，并为他们提供了一个报告，列出不同的数据点一带的过程。我有一个方法来生成报告，看起来像下面这样。

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中的字段名称将没有任何意义，而且不能有部分（如区域1 ，2区等），我可以报告链接到这一点，但报告的宽度只能是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?

开放给任何建议。谢谢！

Open to any suggestions. Thanks!

推荐答案

在你的问题，你说

我有一种方法来生成一个报告，如下所示

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

现在也许该数据可能已经在非转动的形式的地方（不同的值 S在不同的行），但如果没有，那么你会使用类似下面的查询，实现了。

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

...返回：

...returning:

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];

...回到...

...returning...

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