Transpose (Access 2002)

Topic

New Reply

I’m trying to find a way where I can transpose data that I have in a table. Does anyone know how I can do this?
Please see the attached Excel file for a visual explaination.

Thanks,
Drew

Reply | Quote

Replies

You can use DAO (or ADO) code to loop through the records and fields of the source table, and create records in the target table. See the thread beginning at post 229479 for an example; the next to last post in the thread contains a link to Roger’s Access Library where you can find a demo that may help.

Reply | Quote

Hans,

Can you please walk me through how I’m suppose to get this to work? My knowledge of VB if VERY limited, so I get stuck easy.

Thanks,
Drew

Reply | Quote

I have attached a demo (Access 2000 format, zipped) based on your example tables. It uses DAO, so if you use the code in your own database, you must select Tools | References… in the Visual Basic Editor and make sure that a reference to the Microsoft DAO 3.6 Object Library is set. Here is the code:

Sub Normalize()
‘ Declarations
Dim dbs As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim i As Integer

‘ Always set error handling when dealing with objects
On Error GoTo ErrHandler

‘ Set object variables
Set dbs = CurrentDb
Set rstSource = dbs.OpenRecordset(“tblSource”, dbOpenForwardOnly)
Set rstTarget = dbs.OpenRecordset(“tblTarget”, dbOpenDynaset)

‘ Loop through source records
Do While Not rstSource.EOF
For i = 1 To 11
‘ Add new record to target table
rstTarget.AddNew
‘ Set the fields
rstTarget![ITEM_ID] = rstSource![ITEM_ID]
rstTarget![SHORT DESC] = rstSource![SHORT DESC]
rstTarget![LONG DESC] = rstSource.Fields(“LONG DESC ” & i)
rstTarget.Update
Next i
‘ And on to the next
rstSource.MoveNext
Loop

ExitHandler:
‘ Make sure that recordsets are closed
‘ and object memory is released
On Error Resume Next
rstTarget.Close
Set rstTarget = Nothing
rstSource.Close
Set rstSource = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
‘ Inform user, then clean up
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

In your own database, you must replace “tblSource” and “tblTarget” by the names of your tables. You must already have designed the target table. If the field names in your spreadsheet were just examples, you must adapt the code to use the correct names.

Oh yeah, to run the code in the demo database, activate the Visual Basic Editor (Alt+F11), open the module basNormalize if necessary, click somewhere in the procedure and press F5. Execution in the demo is instantaneous, but for a table with thousands of records, it may take some time.

Reply | Quote

Worked perfect! Thank you very much.
Just out of curiosity, would it be tough to take this back to a normalized table (original)?

Regards,
Drew

Reply | Quote

It is possible to write similar code to denormalize, i.e. to reconstruct the original table. There is a sample database from Roger’s Access Library that shows both how to normalize and how to denormalize a table.

Reply | Quote

It is possible to write similar code to denormalize, i.e. to reconstruct the original table. There is a sample database from Roger’s Access Library that shows both how to normalize and how to denormalize a table.

Reply | Quote

Hans,
I have a similar table that I’m trying to transpose(See Spreadsheet)
The difference is that every 6th row I have to start over again and populate the columns.
If I step through my code I can get it to work for the first model in the example and up to the first column of the second model. My problem is getting back to the 6th, 12th… row when I loop to edit the row.

Function CreateExcelTable()

Dim db As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim V, W, X, Y, Z As Integer
Dim strSQL As String
Dim strNAME, strNAME1, strWHERE As String
Dim qdf As QueryDef
Dim strFieldCount As Integer

    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryPerformance", dbOpenDynaset)
    strFieldCount = rst.Fields.Count
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete tblExcel.* From tblExcel;"
    DoCmd.SetWarnings True
    Set rst1 = db.OpenRecordset("tblExcel")
        W = 1
        X = 1
        Y = 1
        Z = 1
             Do While Not rst.EOF
                      Do Until X = 6
                            Select Case W
                                Case 1
                                    rst1.AddNew
                                 Case 2
                                    rst1.Edit
                            End Select
                                rst1!Model = rst!Model
                                rst1!EndDate = rst!End_Date
                                rst1!Item = rst.Fields(Y).Name
                            Select Case Z
                                Case 1
                                    rst1![175] = rst.Fields(Y).Value
                            End Select
                            Y = Y + 1
                            rst1.Update
                            
                            X = X + 1
                            If Z > 1 Then rst1.MoveNext
                            V = V + 1
                     Loop 'x = 6
                             rst.MoveNext
                             
                             Select Case V
                                Case Is < 25
                                    rst1.MoveFirst
                                    W = 2
                                    Z = Z + 1
                                Case Else
                                
                                    Z = 1
                                    V = 1
                                    W = 1
                            End Select
                             X = 1
                             Y = 1
        Loop

End Function

I’m getting loopy trying to figure this out.
Scott

Reply | Quote

Hans happens to be on vacation at the moment, but one potential problem with your code is that you are deleting everything in tblExcel and then opening a recordset on that table and saying “Do While Not rst.EOF”, which means the code should never execute, so I don’t know how you’re getting it to run the first time.

Reply | Quote

If you look a little closer you will find that rst is based upon a query qryPerformance and that rst1 is used to add records.

Reply | Quote

You’re right, Pat. I missed that. It was early this morning, and I only glanced in passing.

Reply | Quote

Here is the db with the code and the tables if anyone wants to take a look.
Thanks,
Scott

Reply | Quote

A quick debug of the code and I see the following :
In the Select Case V, Case Else you set W = 3. In the Select Case W there is no Case 3. So you don’t have an append or an edit.
That’s why you get an error in line: rst1!Model = rst!Model ”””errors on this line
If I change W=1, the code run but you have another problem:
In the Select Case V, Case is <25, you do a rst1.MoveFirst. This is wrong, you should do a rst.FindFirst for the value of ModelY

Hope this help.

Reply | Quote

Francois,
I used a 3 because I used the AddNew in the Select Case V statement.
I’ll see if I can figure out the FindFirst you suggested.
Thanks

Reply | Quote

Scott,
In attachment the db I have modified.
Change w to 1
Add Findfirst
Change open recordset to add dbOpenDynaset
and modify your dim statements. For dim’s you have to declare each variable with an as … , otherwise they will be declared as variant.
This is doing the job I think.

Reply | Quote

Francois,
Thank you, Thank you. I don’t do enough with recordsets to be fully proficient in them.
I can rest my brain for awhile.
A couple of questions. What do I gain with the dbOpenDynaset ? I thought that was used for large recordsets.
Also I removed the rst1.AddNew from the Case Else of the Select Case V statement since it is taken care of at the beginning of the loop.
Thanks again for your work, I owe you one…or two

Reply | Quote

You open a recordset dbOpenDynaset to have the possibility to do a findfirst. In a recordset dbOpenTable (the default) you can’t do this.

Reply | Quote

You open a recordset dbOpenDynaset to have the possibility to do a findfirst. In a recordset dbOpenTable (the default) you can’t do this.

Reply | Quote

Francois,
Thank you, Thank you. I don’t do enough with recordsets to be fully proficient in them.
I can rest my brain for awhile.
A couple of questions. What do I gain with the dbOpenDynaset ? I thought that was used for large recordsets.
Also I removed the rst1.AddNew from the Case Else of the Select Case V statement since it is taken care of at the beginning of the loop.
Thanks again for your work, I owe you one…or two

Reply | Quote

Scott,
In attachment the db I have modified.
Change w to 1
Add Findfirst
Change open recordset to add dbOpenDynaset
and modify your dim statements. For dim’s you have to declare each variable with an as … , otherwise they will be declared as variant.
This is doing the job I think.

Reply | Quote

Francois,
I used a 3 because I used the AddNew in the Select Case V statement.
I’ll see if I can figure out the FindFirst you suggested.
Thanks

Reply | Quote

A quick debug of the code and I see the following :
In the Select Case V, Case Else you set W = 3. In the Select Case W there is no Case 3. So you don’t have an append or an edit.
That’s why you get an error in line: rst1!Model = rst!Model ”””errors on this line
If I change W=1, the code run but you have another problem:
In the Select Case V, Case is <25, you do a rst1.MoveFirst. This is wrong, you should do a rst.FindFirst for the value of ModelY

Hope this help.

Reply | Quote

Here is the db with the code and the tables if anyone wants to take a look.
Thanks,
Scott

Reply | Quote

You’re right, Pat. I missed that. It was early this morning, and I only glanced in passing.

Reply | Quote

If you look a little closer you will find that rst is based upon a query qryPerformance and that rst1 is used to add records.

Reply | Quote

Hans happens to be on vacation at the moment, but one potential problem with your code is that you are deleting everything in tblExcel and then opening a recordset on that table and saying “Do While Not rst.EOF”, which means the code should never execute, so I don’t know how you’re getting it to run the first time.

Reply | Quote

Hans,
I have a similar table that I’m trying to transpose(See Spreadsheet)
The difference is that every 6th row I have to start over again and populate the columns.
If I step through my code I can get it to work for the first model in the example and up to the first column of the second model. My problem is getting back to the 6th, 12th… row when I loop to edit the row.

Function CreateExcelTable()

Dim db As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim V, W, X, Y, Z As Integer
Dim strSQL As String
Dim strNAME, strNAME1, strWHERE As String
Dim qdf As QueryDef
Dim strFieldCount As Integer

    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryPerformance", dbOpenDynaset)
    strFieldCount = rst.Fields.Count
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete tblExcel.* From tblExcel;"
    DoCmd.SetWarnings True
    Set rst1 = db.OpenRecordset("tblExcel")
        W = 1
        X = 1
        Y = 1
        Z = 1
             Do While Not rst.EOF
                      Do Until X = 6
                            Select Case W
                                Case 1
                                    rst1.AddNew
                                 Case 2
                                    rst1.Edit
                            End Select
                                rst1!Model = rst!Model
                                rst1!EndDate = rst!End_Date
                                rst1!Item = rst.Fields(Y).Name
                            Select Case Z
                                Case 1
                                    rst1![175] = rst.Fields(Y).Value
                            End Select
                            Y = Y + 1
                            rst1.Update
                            
                            X = X + 1
                            If Z > 1 Then rst1.MoveNext
                            V = V + 1
                     Loop 'x = 6
                             rst.MoveNext
                             
                             Select Case V
                                Case Is < 25
                                    rst1.MoveFirst
                                    W = 2
                                    Z = Z + 1
                                Case Else
                                
                                    Z = 1
                                    V = 1
                                    W = 1
                            End Select
                             X = 1
                             Y = 1
        Loop

End Function

I’m getting loopy trying to figure this out.
Scott

Reply | Quote

I have attached a demo (Access 2000 format, zipped) based on your example tables. It uses DAO, so if you use the code in your own database, you must select Tools | References… in the Visual Basic Editor and make sure that a reference to the Microsoft DAO 3.6 Object Library is set. Here is the code:

Sub Normalize()
‘ Declarations
Dim dbs As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim i As Integer

‘ Always set error handling when dealing with objects
On Error GoTo ErrHandler

‘ Set object variables
Set dbs = CurrentDb
Set rstSource = dbs.OpenRecordset(“tblSource”, dbOpenForwardOnly)
Set rstTarget = dbs.OpenRecordset(“tblTarget”, dbOpenDynaset)

‘ Loop through source records
Do While Not rstSource.EOF
For i = 1 To 11
‘ Add new record to target table
rstTarget.AddNew
‘ Set the fields
rstTarget![ITEM_ID] = rstSource![ITEM_ID]
rstTarget![SHORT DESC] = rstSource![SHORT DESC]
rstTarget![LONG DESC] = rstSource.Fields(“LONG DESC ” & i)
rstTarget.Update
Next i
‘ And on to the next
rstSource.MoveNext
Loop

ExitHandler:
‘ Make sure that recordsets are closed
‘ and object memory is released
On Error Resume Next
rstTarget.Close
Set rstTarget = Nothing
rstSource.Close
Set rstSource = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
‘ Inform user, then clean up
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

In your own database, you must replace “tblSource” and “tblTarget” by the names of your tables. You must already have designed the target table. If the field names in your spreadsheet were just examples, you must adapt the code to use the correct names.

Oh yeah, to run the code in the demo database, activate the Visual Basic Editor (Alt+F11), open the module basNormalize if necessary, click somewhere in the procedure and press F5. Execution in the demo is instantaneous, but for a table with thousands of records, it may take some time.

Reply | Quote

Hans,

Can you please walk me through how I’m suppose to get this to work? My knowledge of VB if VERY limited, so I get stuck easy.

Thanks,
Drew

Reply | Quote

You can use DAO (or ADO) code to loop through the records and fields of the source table, and create records in the target table. See the thread beginning at post 229479 for an example; the next to last post in the thread contains a link to Roger’s Access Library where you can find a demo that may help.

Reply | Quote