Transposing field data by subgroup 1

[TrekBiker]

I have starting data in the form

Category 1, Company 1, Comment1
Category 1, Company 1, Comment2
Category 1, Company 1, Comment3
Category 1, Company 1, Comment4
Category 1, Company 2, Comment5
Category 1, Company 2, Comment6
Category 1, Company 2, Comment7
Category 1, Company 2, Comment8
Category 1, Company 2, Comment9

Category 2, Company 3, Comment10
Category 2, Company 3, Comment11
Category 2, Company 3, Comment12
Category 2, Company 3, Comment13
Category 2, Company 3, Comment14
Category 2, Company 3, Comment15
Category 2, Company 4, Comment16
Category 2, Company 4, Comment17
Category 2, Company 4, Comment18
Category 2, Company 4, Comment19
etc.

There are many Categories, each with varying numbers of Companies.

For part of the analysis I want the data to be in the form

Category 1, Company 1, Comment 1, Comment 2, Comment 3, Comment 4
Category 1, Company 2, Comment 5, Comment 6, Comment 7, Comment 8, Comment 9
Category 2, Company 3, Comment 10, Comment 11, Comment 12, Comment 13, Comment 14, Comment 15
Category 2, Company 4, Comment 16, Comment 17, Comment 18, Comment 19

So for all combinations of Category and Company, transpose the comments and then take out duplicate rows.

Any suggestions welcomed.

 

[dhookom]

Is this Access or Excel? There is a generic concateneate function in the Microsoft Access Queries forum.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Thanks for quick response Duane. The data originates in Excel but I process it in Access. So the operation I'm looking for could be in either.

You pointed me at an Access cross tab query after my similar question in the Access forum but unfortunately the Comment fields are Long Text and the crosstab wizard doesn't recognise them as usable.

 

[dhookom]

Did you try this faq701-4233?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Use the Data > Remove duplicates feature in Excel.

 

[dhookom]

Skip,
Is there some way to remove duplicates but retain the detailed comments for each group?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Do the process on the entire table.

 

[dhookom]

When I had the entire table selected, it suggest there were "no duplicate values found".

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

So what table did you perform on? The OPs example table has no duplicates that I can observe.

Of course, "etc." can imply lots of possibilities. ;-)

 

[dhookom]

I first attempted to select the first two columns but I lost most of the comments. Then I selected all the columns and got the error message.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

I need to explain what I meant by duplicates as it served to confuse.

The operations I'm looking for will greatly reduce the numbers of rows. In my original question it went down from 19 to 4 for the first sets of data.

Doing the operation block by block in Excel and transposing all comments onto say the top row of each block meant having to take out the remaining rows, eg

Category 1, Company 1, Comment1
Category 1, Company 1, Comment2
Category 1, Company 1, Comment3
Category 1, Company 1, Comment4

to

Category 1, Company 1, Comment 1, Comment 2, Comment 3, Comment 4
Category 1, Company 1
Category 1, Company 1
Category 1, Company 1

And by the way, I don't want to concatenate the comments into a single field but keep them as separate fields for later reports.

 

[SkipVought]

Please post a sample of a duplicate.

 

[dhookom]

Since you want to "keep them as separate fields for later reports" you could use a multi-column subreport.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Skip

I think the duplicates thing is becoming a bit of a red herring.

If I were able to do an operation on the first row ..
Category 1, Company 1, Comment1

.. that took account of the other ones in the group to produce
Category 1, Company 1, Comment 1, Comment 2, Comment 3, Comment 4

.. then did the same on other rows in the group I'd get duplicates since in my example all four of the first block's output would look like this.

Duane

Since embarking on this the client has asked for the output in csv format so I can't use the reports options in Access (for other purposes I'll need to read your previous forum threads about multi-column subreports as I see you've explained them in detail).

Thanks to you both for taking time on this.

 

[dhookom]

You could also use code to create a temporary table with the required number of comment columns. Then loop through the original records to populate the temporary table.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Now that sounds promising. Are there any examples you can point me at?

 

[SkipVought]

In Excel it can be done on a sheet.

The overall process:

IMPORT .csv in the IMPORT sheet. This can be Refreshed when new .csv appears.
It will also help to include a helper column that concatenates Category & Company

On the SUMMARY sheet use MS Quert via Data > Get external data > From others sources > Microsoft Query > select Excel Files* and drill down to your master workbook. Do a query to Select distance Category, Company, Count(*). The count will inform you the number of columns for comments for each. File > return data to Excel.

I'm on my iPad so I can't construct a working formula, but I'd use the INDEX(), MATCH(), OFFSET(), COUNTIF() functions to define the range for each Category/Company, count the number of rows for each, define the offset relative to the column in the SUMMARY table. That's the best I can do at this time.

 

[dhookom]

If you want to stay in Access/VBA you can try the function with your actual table and field names:

Public Function CreateCommentSheet()
    On Error GoTo errCreate
    Dim strSQL As String
    Dim intMaxComments As Integer
    Dim intCommentNum As Integer
    [COLOR=#4E9A06]'category, company, comment[/color]
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strCompany As String
    Dim strCategory As String
    Set db = CurrentDb
    strSQL = "SELECT TOP 1 Count(Category) AS MaxComments " & _
        "from tblTrekBiker " & _
        "GROUP BY Category, Company " & _
        "ORDER BY Count(Category) DESC"
    Set rs = db.OpenRecordset(strSQL)
    intMaxComments = rs(0)
    [COLOR=#4E9A06]'Create the table to store the comments with multiple comment fields[/color]
    strSQL = "SELECT Category, Company "
    For intCommentNum = 1 To intMaxComments
        strSQL = strSQL & " ,  First(Comment) AS Comment" & intCommentNum
    Next
    strSQL = strSQL & " INTO ttblCatCompanyComments " & _
    "from tblTrekBiker " & _
    "GROUP BY tblTrekBiker.Category, tblTrekBiker.Company;"
    CurrentDb.TableDefs.Delete ("ttblCatCompanyComments")
    DoCmd.SetWarnings False
    db.Execute strSQL, dbFailOnError
    [COLOR=#4E9A06]'create update query to set all the comment columns to nulls[/color]    strSQL = "UPDATE ttblCatCompanyComments SET "
    For intCommentNum = 1 To intMaxComments
        strSQL = strSQL & " Comment" & intCommentNum & " = Null, "
    Next
    strSQL = Left(strSQL, Len(strSQL) - 2)
   [COLOR=#4E9A06] 'Debug.Print strSQL[/color]    db.Execute strSQL, dbFailOnError
    DoCmd.SetWarnings True
    [COLOR=#4E9A06]' loop through the detail records and update the appropriate comment field based on Category and Company[/color]
    strSQL = "SELECT Category, Company, Comment from tblTrekBiker ORDER BY Category, Company"
    Set rs = db.OpenRecordset(strSQL)
    With rs
        intCommentNum = 1
        Do Until .EOF
            If strCompany <> !company Or strCategory <> !Category Then
                strCompany = !company
                strCategory = !Category
                intCommentNum = 1
             Else
                intCommentNum = intCommentNum + 1
            End If
            strSQL = "Update ttblCatCompanyComments SET Comment" & intCommentNum & " = """ & !Comment & _
                """ WHERE Company = """ & strCompany & """ AND Category = """ & strCategory & """ "
            db.Execute strSQL, dbFailOnError
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
Exit Function

errCreate:
    Select Case Err.Number
        Case 3265
            Resume Next
        Case Else
        MsgBox Err.Number & " " & Err.Description
            Resume Next
    End Select
End Function

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Duane

This is looking really good but I'm getting a couple of errors. First at line CurrentDb.TableDefs.Delete ("ttblCatCompanyComments") if the table doesn't exist. Can get round this.

Then stops at db.Execute strSQL, dbFailOnError saying
Run-time Error 3061 "Too few parameters. Expected 1"

Unfortunately I'm away until tomorrow evening so can't spend time resolving this. Really appreciate your help, and star coming up.

 

[dhookom]

Your field names are probably different. Please share significant details.

Duane
Hook'D on Access
MS Access MVP