Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Transpose Columns Names with Data

Posted on 2009-02-16
Medium Priority
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
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 23651195
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

ID: 23651252

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 colour......as 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23651298
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

578 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