Vertical Rows to Horizontal Columns

[ChristineMD]

I have a query that generates items and their category codes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For these items there could be up to 7 unique category codes, but all will likely NOT have all 7<o></o>
So I have a table that has:<o></o>
Item CatCode<o></o>
Item1 12345<o></o>
Item1 12346<o></o>
Item2 12341 <o></o>
Item2 12342<o></o>
Item2 12346<o></o>
Item4 12344<o></o>
<o></o>
I want to get this data into a horizontal format with one record per ITEM, with its up to 7 categories in separate columns<o></o>
Item Cat1 Cat2 Cat3 (etc-->)
Item1 12345 12346<o></o>
Item2 12341 12342 12346<o></o>
Item4 12344<o></o>
<o></o>
I've taken a stab at modifying a piece of code I have to see if I can get it to work for this purpose. Can this work? I think I'm missing some pieces about defining an array possibly. It's the if loop and using the loop counter to define and populate the variable I'm not sure of. You guys and gals are always so helpful. I appreciate the look in advance!

Public Function outletcats() As Boolean
    On Error Resume Next
 
    Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
    Dim strdisplayitem As String, strcat1 As String, strcat2 As String, strcat3 As String, strcat4 As String, strcat5 As String, strcat6 As String, strcat7 As String
 
    cnt = 1
 
    Set db = CurrentDb()
    'Call RecreateTables(db)
    sSQL = "DELETE FROM tOutletCatsFinal"
    db.Execute sSQL
 
    sSQL = "SELECT displayitem, CatCode FROM tOutletItems2  ORDER BY displayitem ASC"
    Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
    If Not rst.BOF And Not rst.EOF Then
      rst.MoveFirst
      strdisplayitem = rst!displayitem
      strcat(cnt) = rst!CatCode
 
 
      rst.MoveNext
      Do Until rst.EOF
        If strdisplayitem = rst!displayitem Then
          strcat(cnt) = rst!CatCode
 
        Else
          sSQL = "INSERT INTO tOutletCatFinal (displayitem, Cat1, Cat2, Cat3, Cat4, Cat5, Cat6, Cat7) VALUES('" & strdisplayitem & "','" & strcat1 & "','" & strcat2 & "','" & strcat3 & strcat2 & "','" & strcat4 & "','" & strcat5 & "','" & strcat6 & "','" & strcat7 & "','" & "')"
          db.Execute sSQL
            strdisplayitem = rst!displayitem
            strcat(cnt) = rst!CatCode
 
      End If
        rst.MoveNext
        cnt = cnt + 1
      Loop
 
      ' Insert Last Record
      sSQL = "INSERT INTO tOutletCatFinal (displayitem, Cat1, Cat2, Cat3, Cat4, Cat5, Cat6, Cat7) VALUES('" & strdisplayitem & "','" & strcat1 & "','" & strcat2 & "','" & strcat3 & strcat2 & "','" & strcat4 & "','" & strcat5 & "','" & strcat6 & "','" & strcat7 & "','" & "')"
      db.Execute sSQL
    End If
 
    Set rst = Nothing
    Set db = Nothing
End Function

 

[jackd]

This may help you with the concatenation.
http://allenbrowne.com/func-concat.html

 

[Kreszch68]

Hi, you can do this using the next SQL statement.
I tried to use your field and table names from the code you posted, but I'm not sure if all names are correct.

TRANSFORM First(CatCode) AS NewCatCode
SELECT DisplayItem
FROM tOutletItems2
GROUP BY DisplayItem
PIVOT CatCode

 

[ChristineMD]

Thanks to both for your replies, never even got a chance to come back here til now - will check both out - thanks again!