J
john
Hi,
please can anyone help me with advice or best still code to transpose a
table in access.
cheers
please can anyone help me with advice or best still code to transpose a
table in access.
cheers
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Amy Vargo
Hi,
My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
The following article shows you how to transpose data in a table.
182822 ACC: How to Transpose Data in a Table or Query
http://support.microsoft.com/?id=182822
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 an Access Basic or Visual Basic for
Applications procedure to accomplish this task.
MORE INFORMATION
================
Note that neither method 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.
If you are using Microsoft Access 2.0, follow these steps to 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 dialog box, click the version of Microsoft Excel to
which you want to export the data, and then click OK.
4. In the Select Microsoft Access Object dialog box, make sure that the
object you want to export is selected. Click OK.
5. In the Export To File dialog box, specify the location and file
name, and then click OK.
If you are using Microsoft Access version 7.0 or later, follow these steps
to 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 Save As/Export.
3. In the Save As dialog box, click To An External File Or Database,
and then click OK.
4. In the Save Table <tablename> In dialog box, select the version of
Excel to which you are exporting in the Save As Type box.
5. Specify the name and location of the file, and then click Export.
Transpose the Data in Microsoft Excel.
Follow these steps to 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, select the Transpose check box and
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 7.0 database. For more information, please see the
following article in the Microsoft Knowledge Base:
KBLink:148165.KB.EN-US: ACC95: Import Spreadsheet Wizard Cannot Append
Data to Table
If you are using Microsoft Access 2.0, follow these steps to import the
transposed data into Microsoft Access:
1. On the File menu, click Import.
2. In the Import dialog box, click the appropriate version of Microsoft
Excel, and then click OK.
3. In the Select File dialog box, locate and select the spreadsheet
file containing the transposed data, and then click Import.
4. In the Import Spreadsheet dialog box, select the sheet containing
the transposed data from the Sheet Name list. Do not select the First
Row Contains Field Names check box.
5. If you previously created a table consisting entirely of text
fields, select that table from the Append To Existing Table list;
otherwise, click Create New Table. Click OK.
If you are using Microsoft Access version 7.0 or later, follow these steps
to import the transposed data into 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 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 in
the second screen of the Import Spreadsheet Wizard. Click Next.
6. If you are using Microsoft Access version 7.0, click Next. If you
are using Microsoft Access 97 and you previously created a table
consisting entirely of text fields, select that table from the Existing
table list; otherwise, click In A New Table, and then click Next twice.
7. If you are importing into a new table, click No Primary Key.
8. Click Finish. The resulting table contains the transposed data.
Method 2 - Use a Custom Function to Transpose the Table
-------------------------------------------------------
This method assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access version 2.0. For more information about Access Basic, please refer
to the "Building Applications" manual.
1. If you are using Microsoft Access 2.0, create a new module in your
database and enter the following procedure:
Function Transposer(strSource As String, strTarget As String)
Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As 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 - 1
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), DB_TEXT)
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
rstTarget.AddNew
rstTarget.Fields(0) = rstSource.Fields(i).Name
rstTarget.Update
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
rstTarget.Edit
rstTarget.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
rstTarget.Update
Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j
rstSource.Close
rstTarget.Close
db.Close
Exit Function
Transposer_Err:
Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3011
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Error) & " " & Error(Err)
End Select
Exit Function
End Function
If you are using Microsoft Access version 7.0 or later, create a new
module in your database and enter the following procedure:
Function Transposer(strSource As String, strTarget As String)
Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As 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
2. To test the function, open the Debug window (or the Immediate window
in Microsoft Access version 2.0). If you are in the sample database
Northwind.mdb (or Nwind.mdb), for example, and you want to transpose
the Suppliers table, type the following line, and then press ENTER:
"?Transposer("Suppliers","SuppliersTrans")" (without the quotation
marks)
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Amy Vargo
Microsoft Access Engineer
This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
The following article shows you how to transpose data in a table.
182822 ACC: How to Transpose Data in a Table or Query
http://support.microsoft.com/?id=182822
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 an Access Basic or Visual Basic for
Applications procedure to accomplish this task.
MORE INFORMATION
================
Note that neither method 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.
If you are using Microsoft Access 2.0, follow these steps to 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 dialog box, click the version of Microsoft Excel to
which you want to export the data, and then click OK.
4. In the Select Microsoft Access Object dialog box, make sure that the
object you want to export is selected. Click OK.
5. In the Export To File dialog box, specify the location and file
name, and then click OK.
If you are using Microsoft Access version 7.0 or later, follow these steps
to 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 Save As/Export.
3. In the Save As dialog box, click To An External File Or Database,
and then click OK.
4. In the Save Table <tablename> In dialog box, select the version of
Excel to which you are exporting in the Save As Type box.
5. Specify the name and location of the file, and then click Export.
Transpose the Data in Microsoft Excel.
Follow these steps to 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, select the Transpose check box and
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 7.0 database. For more information, please see the
following article in the Microsoft Knowledge Base:
KBLink:148165.KB.EN-US: ACC95: Import Spreadsheet Wizard Cannot Append
Data to Table
If you are using Microsoft Access 2.0, follow these steps to import the
transposed data into Microsoft Access:
1. On the File menu, click Import.
2. In the Import dialog box, click the appropriate version of Microsoft
Excel, and then click OK.
3. In the Select File dialog box, locate and select the spreadsheet
file containing the transposed data, and then click Import.
4. In the Import Spreadsheet dialog box, select the sheet containing
the transposed data from the Sheet Name list. Do not select the First
Row Contains Field Names check box.
5. If you previously created a table consisting entirely of text
fields, select that table from the Append To Existing Table list;
otherwise, click Create New Table. Click OK.
If you are using Microsoft Access version 7.0 or later, follow these steps
to import the transposed data into 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 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 in
the second screen of the Import Spreadsheet Wizard. Click Next.
6. If you are using Microsoft Access version 7.0, click Next. If you
are using Microsoft Access 97 and you previously created a table
consisting entirely of text fields, select that table from the Existing
table list; otherwise, click In A New Table, and then click Next twice.
7. If you are importing into a new table, click No Primary Key.
8. Click Finish. The resulting table contains the transposed data.
Method 2 - Use a Custom Function to Transpose the Table
-------------------------------------------------------
This method assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access version 2.0. For more information about Access Basic, please refer
to the "Building Applications" manual.
1. If you are using Microsoft Access 2.0, create a new module in your
database and enter the following procedure:
Function Transposer(strSource As String, strTarget As String)
Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As 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 - 1
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), DB_TEXT)
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
rstTarget.AddNew
rstTarget.Fields(0) = rstSource.Fields(i).Name
rstTarget.Update
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
rstTarget.Edit
rstTarget.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
rstTarget.Update
Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j
rstSource.Close
rstTarget.Close
db.Close
Exit Function
Transposer_Err:
Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3011
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Error) & " " & Error(Err)
End Select
Exit Function
End Function
If you are using Microsoft Access version 7.0 or later, create a new
module in your database and enter the following procedure:
Function Transposer(strSource As String, strTarget As String)
Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As 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
2. To test the function, open the Debug window (or the Immediate window
in Microsoft Access version 2.0). If you are in the sample database
Northwind.mdb (or Nwind.mdb), for example, and you want to transpose
the Suppliers table, type the following line, and then press ENTER:
"?Transposer("Suppliers","SuppliersTrans")" (without the quotation
marks)
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Amy Vargo
Microsoft Access Engineer
This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.