We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Transpose Columns Names with Data

keilah
keilah asked
on
Medium Priority
526 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.



CERTIFIED EXPERT
Top Expert 2016

Commented:
i don't know exactly what you mean, the values in the Columnname , starts with Window and ends in Colour for a particular JobID
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.