Hi,

I am trying to transpose a table as below (underlined = field names - not data):

Type of transaction Company1 Company2 Company3
Transaction1 10 40 70
Transaction2 20 50 80
Transaction3 30 60 90

to

Company Code Transaction1 Transaction2 Transaction3
Company1 10 20 30
Company2 40 50 60
Company3 70 80 90

What the MS knowledgebase example (http://support.microsoft.com/?kbid=283875) currently produces is:

Column1 Column2 Column3 Column4
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.

Thanks for your assistance.

Just wondering if anyone could help with this. Below is the code from the MS knowledge base to transpose. 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

But the above code does not work... Any suggestions?

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

... 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'".

Thoughts?

What are you trying to do with this code? What is runSQL?

What are you trying to do with this code? What is runSQL?

I am trying to transpose the table shown in the initial post. The MS knowledge base has code to do this, however that code does not actually rename the fields with the contents of the first field. (See table examples above).

runSQL is used in VBA to run specific SQL statements. I am trying to use runSQL to return the contents of the first record/cell and then in turn rename the fields using this string.

No-one got any advice??

Can't you use a crosstab query?

Can't you use a crosstab query?
Nope, I have 250 columns and 9 records in this table (yes its not great table design etc etc, but I didn't design the source system, and I need to find a way to convert it into a logical format, hence why I want to transpose it.)