Pivot on multiple fields and export from Access

Question

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

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.

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!

Answer 1

In your question you say that

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

and then list the data as

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

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

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