title: Pivot on multiple fields and export from Access
body: <p>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.</p>
  
  <pre><code> Batch     Zone    Value1     Value 2   etc.
   25        1       5          15
   25        2       12         31
   26        1       6          14 
   26        2       10         32
  </code></pre>
  
  <p>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...</p>
  
  <pre><code>                Zone 1                Zone 2
   Batch     Value1     Value2     Value1     Value2
   25        5          15         12         31
   26        6          14         10         32
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Open to any suggestions.  Thanks!</p>