Transpose Columns Names with Data

Posted on 2009-02-16
Last Modified: 2012-05-06
Hi Experts

I need to ALSO transpose the column names/headers along side the score values so add the column names to the right of Proficiency, as shown on the transpose table.

Can this be done with the current macro on the db.file
Question by:keilah
    LVL 119

    Accepted Solution

    just add a field to table Transposed_Data name it ColumnName
    then use this codes

    Sub transposeT()
    Dim rs As DAO.Recordset, rs1 As DAO.Recordset
    Dim fldArr(), j As Integer, i As Integer
    Set rs = CurrentDb.OpenRecordset("Jobs_Data_List")
    Set rs1 = CurrentDb.OpenRecordset("Transposed_Data")
        For i = 0 To rs.Fields.Count - 1
            ReDim Preserve fldArr(i)
            fldArr(i) = rs.Fields(i).Name
    Do Until rs.EOF
        For j = 4 To UBound(fldArr)
            If rs(fldArr(j)).Value > 0 Then
                With rs1
                    rs1![Job ID] = rs![Job ID]
                    rs1![Job Name] = rs![Job Name]
                    rs1![Job Group] = rs![Job Group]
                    rs1![Job Skillpool Group] = rs![Job Skillpool Group]
                    rs1![Proficiency] = rs.Fields(fldArr(j))
                    rs1![ColumnName] = rs.Fields(fldArr(j)).Name
                End With
            End If

    End Sub

    Author Comment


    Thanks workls prefcetly, is there a way to keep the order of the columns so that it starts with the first and column i.e window and end with shown in the demo file. At present the column information is random in the end result and no in order as shown in the table we are transposing.

    LVL 119

    Expert Comment

    by:Rey Obrero
    i don't know exactly what you mean, the values in the Columnname , starts with Window and ends in Colour for a particular JobID

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now