How to Transpose table in MS Access using VBA code

How to Transpose table in MS Access using VBA code

This function is used to achieve Transpose of table in MS Access using VBA code.

Procedure :-

1. Create a module named modRowToColumn. Following code you need to copy & paste in module then save it.

Function RowToColumnTable()
Dim db As Database
Dim sqlFleet, sqlTotal, sqlAccredited, sSQL As String
Dim rsFleet As Recordset
Dim rsTotal As Recordset
Dim rsAccredited As Recordset
Dim i, j, k As Integer
Dim RowToColumnWithDataType, RowToColumn, varTotal, varAccredited As String

Set db = CurrentDb()
sqlFleet = "SELECT  tblTranspose.* FROM tblTranspose WHERE (((tblTranspose.[1])='Fleet'));"
Set rsFleet = db.OpenRecordset(sqlFleet)

sqlTotal = "SELECT  tblTranspose.* FROM tblTranspose WHERE (((tblTranspose.[1])='Total'));"
Set rsTotal = db.OpenRecordset(sqlTotal)

sqlAccredited = "SELECT  tblTranspose.* FROM tblTranspose WHERE (((tblTranspose.[1])='Accredited'));"
Set rsAccredited = db.OpenRecordset(sqlAccredited)

i = rsFleet.Fields.Count
k = rsFleet.Fields.Count
j = 0

Do While j < (i - 1)
    RowToColumnWithDataType = RowToColumnWithDataType & rsFleet(j) & " Text(50) ,"
    RowToColumn = RowToColumn & rsFleet(j) & ","
    varTotal = varTotal & "'" & rsTotal(j) & "'" & ","
    varAccredited = varAccredited & "'" & rsAccredited(j) & "'" & ","
    j = j + 1
Loop

If i = k Then
    RowToColumnWithDataType = RowToColumnWithDataType & rsFleet(k - 1) & " Text(50)"
    RowToColumn = RowToColumn & rsFleet(k - 1)
    varTotal = varTotal & rsTotal(k - 1)
    varAccredited = varAccredited & rsAccredited(k - 1)
End If

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblRowToColumn'") = 1 Then
  DoCmd.DeleteObject acTable, "tblRowToColumn"
End If

sSQL = "CREATE TABLE tblRowToColumn (" & RowToColumnWithDataType & ")"
db.Execute sSQL

sSQL = "INSERT INTO tblRowToColumn (" & RowToColumn & ")  " & _
        "VALUES(" & varTotal & ")"
db.Execute sSQL

sSQL = "INSERT INTO tblRowToColumn (" & RowToColumn & ")  " & _
        "VALUES(" & varAccredited & ")"
db.Execute sSQL

MsgBox "Transposition has been done. New Table RowToColumn created with transposed data"
End Function

2. Create a table tblTranspose as shown below.



3. Create a form frmTransposer as shown below



Now under click event of RowToColumn button call function RowToColumnTable() specified in module modRowToColumn.

Private Sub Command0_Click()
    Call RowToColumnTable
End Sub

4. Now execute the application. When you click on RowToColumn button it will create a new table tblRowToColumn with transposed data.



RowToColumnTable() function will change the first Row to Column.

Note :- RowToColumnTable() function will work for first three rows of tblTranspose. In case if you want to transpose a table having record more than three than you need to add code in function RowToColumnTable(). Following code you need to write.

sqlRow4 = "SELECT  tblTranspose.* FROM tblTranspose WHERE (((tblTranspose.[1])='Row4'));"
Set rsRow4 = db.OpenRecordset(sqlRow4)

sqlRow5 = "SELECT  tblTranspose.* FROM tblTranspose WHERE (((tblTranspose.[1])='Row5'));"
Set rsRow5 = db.OpenRecordset(sqlRow5)