Help with loop to convert rows to columns

[VegasUser]

Sorry for being a VBA rookie - I am decidedly in over my head! I've looked for similar code to borrow and didn't see anything similar. Crosstab isn't suited and neither is concatenate.

I’m trying to create a temp table from data that is in rows and I need to convert to columns so I can format a catalog of items with pictures and I need more than one item (row) per detail section

Catalog query results -
CATALOG ITEM PIC
1234 0001 \imagepath\image1.jpg
1234 0002 \imagepath\image2.jpg
1234 0003 \imagepath\image3.jpg
1234 0004 \imagepath\image4.jpg
1234 0005 \imagepath\image5.jpg
1234 0006 \imagepath\image6.jpg
Etc......

I need it to look like……

Temp Catalog Table
CATALOG|GROUP|ITEM1|PIC1|ITEM2|PIC2|ITEM3|PIC3
1234|1|0001|image1.jpg|0002|image2.jpg|0003|image3.jpg
1234|2|0004|image4.jpg|0005|image5.jpg|0006|image6.jpg

I want to put this in a loop, but I don’t know the VBA syntax
***********************************
Define recordset (how?)
SELECT * from table where catalog=[x] ORDER BY item
‘order by item so when they’re written into the temp table, they are already sorted

grp = 1

For each row in recordset
Do
For grp_inc in 1 to 3
do
Item[grp_inc] = currentrow.item
Pic[grp_inc] = currentrow.pic
grp_inc = grp_inc + 1
if grp_inc = 3
then grp = grp + 1 ‘if last loop, inc grp #
end if
nextrow
done
write catalog|grp|item1|pic1|item2|pic2|item3|pic3 to temp table (how?)
grpinc = 1
Done
***************************************
Thank you for helping!

 

[Blorf]

Do you know how to do an update query?

Seems that if you create your structure, with the Catalog as the primary key to prevent duplicates, then you can populate one for each, then an append query that for Pic1 says put in pic1 the jpg path, but only if Item = 0001. Do that four times and you are done.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[VegasUser]

I tried to do an update query, but this is a dynamic report and the criterion isn't static., i.e., catalog 1234 might have 60 rows (items) of different item numbers and catalog 9999 might have 10 items, so I needed a way to loop through the query without hardcoding anything.

i.e., if I run the query for catalog 1234 I get
1234|0001|0001.jpg
1234|9876|9876.jpg
1234|4444|4444.jpg
for x # of rows

In tblTemp this would look like
CATALOG|ITEM1|PIC1|ITEM2|PIC2|ITEM3|PIC3
1234|0001|0001.jpg|9876|9876.jpg|4444|4444.jpg

for catalog 9999 I get
9999|8383|8383.jpg
9999|6789|6789.jpg
9999|4040|4040.jpg
for x # of rows

so tblTemp would have
CATALOG|ITEM1|PIC1|ITEM2|PIC2|ITEM3|PIC3
9999|8383|8383.jpg|6789|6789.jpg|4040|4040.jpg

Here's the dB structure -
tblCatalog
cat_no
cat_name
cat_company
cat_verbiage

tblCatalogItem
cat_no
cat_itemno

tblItems
cat_itemno
item_desc
item_price
item_pic

tblTemp
cat_no
item1
pic1
item2
pic2
item3
pic3

Then I use tblTemp as the record source for my report.

Any ideas?

 

[cmmrfrds]

Are the items numbered consecutively within a catalog number, or would you need to dynamically assign a sequence number.

 

[cmmrfrds]

General idea.
Use a self join to generate a sequence number which will be a subquery that you can think of as a temporary table for the duration of the query.

Select C.cat_no, CI.itemno, Temp.seq
From tblCatalog as C
Inner Join tblCatalogItem as CI
On C.cat_no = CI.cat_no
Inner Join
(Select cat_no count(*) as seq
From tblCatalog as A
Inner Join tblCatalog as B
On A.cat_no <= B.cat_no) as Temp
On Temp.cat_no = C.cat_no
Order by C.cat_no, Temp.seq

Now set up your loop to put the respective items in the temp table using the seq to determine the column number.

 

[cmmrfrds]

Noticed I missed a comma.
(Select cat_no, count(*) as seq
From tblCatalog as A
Inner Join tblCatalog as B
On A.cat_no <= B.cat_no) as Temp

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm

There are several to many or perhaps just A LOT of caveats which mean you shouldn't get into this quagmire (pissmire?). First, it will fail (badly and dramatically!) if the results set generates more than 255 "Fields". From that point, it is just palin Up hill with more and more defficiencies. But mayhap someone would like to use it as a fixer-upper?

Public Function basCreXposeTbl(strSource As String, strTarget As String) As Long

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset
    Dim tblNew As TableDef
    Dim fldNew As DAO.Field
    Dim MyNewFld As String
    Dim MyFld() As MyFldType
    Dim FldLen() As String

    Dim Idx As Long
    Dim Jdx As Long
    Dim Kdx As Long
    Dim MyFldLen As Integer


    On Error GoTo ErrExit

    Set dbs = CurrentDb()
    Set rstSrc = dbs.OpenRecordset(strSource)

    'Get a valid record count from the Source Table
    
    Idx = rstSrc.RecordCount
    ReDim MyFld(Idx)
    ReDim FldLen(Idx)
    rstSrc.MoveFirst

    'First field name Derived from the Field Name of Field(0)
    MyFld(0).FldName = basValdName(rstSrc.Fields(0).Name)

    'Remaiining field names from field(0) values
    Idx = 1
    While Not rstSrc.EOF

        MyFld(Idx).FldName = basValdName(rstSrc.Fields(0).Value)

        rstSrc.MoveNext
        Idx = Idx + 1
    Wend

    'Here with a supposed set of field names in an array.  Need to _
     check for dups and adjust to make sure there are none
    Idx = 0
    While Idx <= UBound(MyFld) - 1
        Kdx = 1

        Jdx = Idx + 1
        While Jdx <= UBound(MyFld) - 1

            If (MyFld(Jdx).FldName = MyFld(Idx).FldName) Then
                'Aparent Dup - so add a suffix
                MyFld(Jdx).FldName = MyFld(Jdx).FldName & "_" & Trim(Str(Kdx))
                Kdx = Kdx + 1
            End If

            Jdx = Jdx + 1
        Wend

        Idx = Idx + 1
    Wend

    'Here to get the field Types and (for Text fields) the Sizes
    Idx = 1
    rstSrc.MoveFirst
    While Not rstSrc.EOF
        MyFldLen = 1
        Jdx = 1

        While Jdx <= rstSrc.Fields.Count - 1

            If (Not IsNull(Len(Trim(rstSrc.Fields(Jdx))))) Then
                MyFldLen = Len(Trim(rstSrc.Fields(Jdx)))
                If (MyFldLen > MyFld(Idx).FldSize) Then
                    MyFld(Idx).FldSize = MyFldLen
                End If
            End If

            Jdx = Jdx + 1
        Wend

        Idx = Idx + 1
        rstSrc.MoveNext
    Wend

    ' Create a new table to hold the transposed data.
    Set tblNew = dbs.CreateTableDef(strTarget)

    'Create a field for each record in the original table, with _
     field names derived from the First Column of the Source Recordset.

    Idx = 0
    While Idx <= UBound(MyFld)

        With tblNew
            .Fields.Append .CreateField(MyFld(Idx).FldName, dbText, MyFld(Idx).FldSize)
        End With

        Idx = Idx + 1
   Wend

    'Actually Save the New table with the fields (all are text!)
    dbs.TableDefs.Append tblNew

    'Show the new table in the dbWindow
    RefreshDatabaseWindow

    Set rstSrc = Nothing
    Set dbs = Nothing

ErrExit:
    basCreXposeTbl = Err

End Function
[code]


MichaelRed