how do I transpose these two fields to rows?

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      
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you will need VBA codes to do this. Is this an option?
SharathData EngineerCommented:
The maximum no. of colors for any user is alway 4?
Rey Obrero (Capricorn1)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"
    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")
Do Until rs.EOF
    Set rs1 = CurrentDb.OpenRecordset("select * from tblburkhardt where fld1='" & rs!fld1 & "'")
    rsNew!fld1 = rs1!fld1
    j = 1
    Do Until rs1.EOF
            j = j + 1

        rsNew("fld" & j) = rs1!fld2

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tom_burkhardtAuthor Commented:
wow. thanks, Capricorn1. I didn't realise until I started playing in Excel that it wasn't just a simple transposition.
tom_burkhardtAuthor Commented:
sorry, Sharath_123, I didn't see your question until I'd already accepted Capricorn's response .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.