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

x
?
Solved

Transpose Columns Names with Data

Posted on 2009-02-16
3
Medium Priority
?
463 Views
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
Transpose-Data.mdb
0
Comment
Question by:keilah
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
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")
rs.MoveFirst
    For i = 0 To rs.Fields.Count - 1
        ReDim Preserve fldArr(i)
        fldArr(i) = rs.Fields(i).Name
    Next
Do Until rs.EOF
    For j = 4 To UBound(fldArr)
        If rs(fldArr(j)).Value > 0 Then
            With rs1
                .AddNew
                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
                .Update
               
            End With
        End If
    Next
rs.MoveNext
Loop
rs.Close
rs1.Close

End Sub
0
 

Author Comment

by:keilah
ID: 23651252
Ok

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.



0
 
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
0

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