ACC2000: How to Transpose Data in a Table or Query

ID: Q202176


The information in this article applies to:

Novice: Requires knowledge of the user interface on single-user computers.


SUMMARY

At times, you may need to transpose the data in a table or query so that the field names are listed vertically down the left column and the data extends across the page horizontally. For example, you may need to transpose the data for a report or before exporting it to a text file. This article shows you two methods that you can use to accomplish this task. Method 1 shows you how you can export the data to Microsoft Excel, transpose the data, and then import the result back into Microsoft Access. Method 2 shows you how you can use a Visual Basic for Applications procedure to accomplish this task.


MORE INFORMATION

NOTE: Neither of the following methods works if you have more than 255 records because the maximum number of fields in a Microsoft Access table is 255.

Method 1 - Transpose the Data in Microsoft Excel

Export the Data to Microsoft Excel

  1. Start Microsoft Access and open your database. In the Database window, click the table or query that you want to export.


  2. On the File menu, click Export.


  3. In the Export Table To dialog box, select the version of Excel to which you are exporting in the Save as Type box.


  4. Specify the name and location of the file, and then click Save.


Transpose the Data in Microsoft Excel

  1. Start Microsoft Excel and open the spreadsheet that you created in step 5 of the "Export the Data to Microsoft Excel" section.


  2. Press CTRL+HOME to go to cell A1. Press CTRL+SHIFT+END to select all of the data.


  3. On the Edit menu, click Copy.


  4. On the Insert menu, click Worksheet.


  5. On the Edit menu, click Paste Special.


  6. In the Paste Special dialog box, click to select the Transpose check box, and then click OK.


  7. On the Format menu, point to Sheet, and then click Rename. Type a name for the sheet that contains the transposed data. If you want, click Save As on the File menu to export the data directly to text from Microsoft Excel.


  8. Save and close the workbook, and then quit Microsoft Excel.


Import the Data into Microsoft Access

NOTE: You can avoid the possibility of type conversion failures if you create a blank table that consists entirely of text fields and you append the data to that table rather than importing the data into a new table. However, you cannot append spreadsheet data to an existing table in a Microsoft Access.
  1. On the File menu, point to Get External Data, and then click Import.


  2. In the Import dialog box, click Microsoft Excel (*.xls) in the Files Of Type list.


  3. Locate and select the file that you saved after transposing the data in Microsoft Excel. Then click Import.


  4. On the first screen of the Import Spreadsheet Wizard, click Show worksheets, and select the worksheet that contains the transposed data; click Next.


  5. Do not select the First Row Contains Column Headings check box on the second screen of the Import Spreadsheet Wizard. Click Next.


  6. Click In A New Table, and then click Next twice.


  7. Click No Primary Key, and then click Next.


  8. Click Finish. The resulting table contains the transposed data.


Method 2 - Use a Custom Function to Transpose the Table

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
  1. Create a new module in your database and enter the following procedure:


  2. 
    Function Transposer(strSource As String, strTarget As String)
    
       Dim db As DAO.Database
       Dim tdfNewDef As DAO.TableDef
       Dim fldNewField As DAO.Field
       Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
       Dim i As Integer, j As Integer
    
       On Error GoTo Transposer_Err
    
       Set db = CurrentDb()
       Set rstSource = db.OpenRecordset(strSource)
       rstSource.MoveLast
    
       ' Create a new table to hold the transposed data.
       ' Create a field for each record in the original table.
       Set tdfNewDef = db.CreateTableDef(strTarget)
       For i = 0 To rstSource.RecordCount
          Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
          tdfNewDef.Fields.Append fldNewField
       Next i
       db.TableDefs.Append tdfNewDef
    
       ' Open the new table and fill the first field with
       ' field names from the original table.
       Set rstTarget = db.OpenRecordset(strTarget)
       For i = 0 To rstSource.Fields.Count - 1
          With rstTarget
            .AddNew
            .Fields(0) = rstSource.Fields(i).Name
            .Update
          End With
       Next i
    
       rstSource.MoveFirst
       rstTarget.MoveFirst
       ' Fill each column of the new table
       ' with a record from the original table.
       For j = 0 To rstSource.Fields.Count - 1
          ' Begin with the second field, because the first field
          ' already contains the field names.
          For i = 1 To rstTarget.Fields.Count - 1
             With rstTarget
                .Edit
                .Fields(i) = rstSource.Fields(j)
                rstSource.MoveNext
                .Update
             End With
    
          Next i
          rstSource.MoveFirst
          rstTarget.MoveNext
       Next j
    
       db.Close
    
       Exit Function
    
    Transposer_Err:
    
       Select Case Err
          Case 3010
             MsgBox "The table " & strTarget & " already exists."
          Case 3078
             MsgBox "The table " & strSource & " doesn't exist."
          Case Else
             MsgBox CStr(Err) & " " & Err.Description
       End Select
    
       Exit Function
    
    End Function 
  3. To test the function, press CTRL+G. In the Immediate window, type the following line, and then press ENTER:


  4. 
    ?Transposer("Suppliers","SuppliersTrans") 

Additional query words: inf reverse turn around set up differently


Keywords          : kbdta IntpOff 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999