Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose (Access 2002)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose (Access 2002)

    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>post 229479</post#> 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.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose (Access 2002)

    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.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose (Access 2002)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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.
    <pre>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![<25] = rst.Fields(Y).Value
    Case 2
    rst1![25_75] = rst.Fields(Y).Value
    Case 3
    rst1![75_125] = rst.Fields(Y).Value
    Case 4
    rst1![125_175] = rst.Fields(Y).Value
    Case 5
    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</pre>



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

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transpose (Access 2002)

    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. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Transpose (Access 2002)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transpose (Access 2002)

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

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

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

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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.
    Francois

  13. #13
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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.
    Francois

  15. #15
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose (Access 2002)

    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. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •