Hi,
I am trying to transpose a table as below (underlined = field names - not data):
[u]Type of transaction Company1 Company2 Company3 to Company250</u>
Transaction1 10 40 70
Transaction2 20 50 80
Transaction3 30 60 90
to
[u]Company Code Transaction1 Transaction2 Transaction3</u>
Company1 10 20 30
Company2 40 50 60
Company3 70 80 90
to Company250
What the MS knowledgebase example (
http://support.microsoft.com/?kbid=283875) currently produces is:
[u]Column1 Column2 Column3 Column4</u>
Type of transaction Transaction1 Transaction2 Transaction3
Company1 10 20 30
Company2 40 50 60
Company3 70 80 90
I suspect it would just be a matter of cycling through the fieldnames, and renaming each one to the contents of the first record, and then deleting the first record. I'm not a VBA guy, and am planning on doing the rest of my project in SQL, but there is no way to transpose a table in SQL that I know of.
The MS Knowledge Base article provides the following code:
Code:
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
I am proposing bolting on an additional piece of code as follows (in pseudo code)
For i = 1 To rstTarget.Fields.Count
rstTarget.Fields.Name = runSQL ("SELECT " & i & " from " & rstTarget & " WHERE 1 = 'Type of Transaction'")
Next i
...and need to find out how if the best way to delete the first record (with the new field names) would just be to use SQL "DELETE FROM " & rstTarget & "WHERE [Company Code] = 'Type of Transaction'".
I know the original table sucks, but I have
no control over that design, and need to work with it in my project, hence why I am trying to transpose the table.
Similar Threads
Transpose Table - Editing the MS KB code
