how do I transpose these two fields to rows?

tom_burkhardt
tom_burkhardt used Ask the Experts™
on
Trying to figure out a way to do this in Access without going to Excel and back.

I have a table myTable which looks like this:
fld1      fld2
bob      blue
bob      green
bob      white
bob      yellow
mike      pink
mike      purple
mike      red

and want it to look like this:
fld1      fld2         fld3        fld4         fld5
bob      blue      green      white      yellow
mike      pink      purple      red      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
you will need VBA codes to do this. Is this an option?
SharathData Engineer

Commented:
The maximum no. of colors for any user is alway 4?
Top Expert 2016
Commented:
this code will handle any number of colors < 254
change the name of table accordingly

field names will be the same as what you posted  fld1,fld2......fldn
Sub createNewFlatTableBurkhardt()
Dim rs As dao.Recordset, j As Integer, i, FldName As String, sFld
Dim rsMax As dao.Recordset, rsNew As dao.Recordset, maxProd
Dim rs1 As dao.Recordset
Set rsMax = CurrentDb.OpenRecordset("select top 1 count(fld1) from tblburkhardt group by fld1 order by count(fld1) desc")
maxProd = rsMax(0)
For i = 1 To maxProd + 1
    FldName = FldName & "," & "fld" & i & " Text"
Next
    FldName = mid(FldName, 2)
If Not IsNull(DLookup("[name]", "msysobjects", "[name]='newtblburkhardt'")) Then
    CurrentDb.Execute "drop table newtblburkhardt"
End If
CurrentDb.Execute "create table newtblburkhardt( " & FldName & ")"
Set rs = CurrentDb.OpenRecordset("select distinct fld1 from tblburkhardt")
Set rsNew = CurrentDb.OpenRecordset("newtblburkhardt")
rs.MoveFirst
Do Until rs.EOF
    Set rs1 = CurrentDb.OpenRecordset("select * from tblburkhardt where fld1='" & rs!fld1 & "'")
    rsNew.AddNew
    rsNew!fld1 = rs1!fld1
    j = 1
    Do Until rs1.EOF
            j = j + 1

        rsNew("fld" & j) = rs1!fld2
        rs1.MoveNext
    Loop
    rsNew.Update
rs.MoveNext
Loop
rs.Close
rs1.Close
rsNew.Close
rsMax.Close

End Sub

Open in new window

Author

Commented:
wow. thanks, Capricorn1. I didn't realise until I started playing in Excel that it wasn't just a simple transposition.

Author

Commented:
sorry, Sharath_123, I didn't see your question until I'd already accepted Capricorn's response .

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial