Grouping data within Microsoft Access or Microsoft Excel

Question

This seems like it should be really simple but I am struggling to find a solution.

I have a large table containing project team members by role, one row for each team member on each project. Here is a simplified sample of what it looks like:

Project	Role	Team Member
Alpha	Project Manager	Will
Alpha	Business Analyst	John
Alpha	Business Analyst	Amy
Alpha	Developer	Sally
Alpha	Developer	Joe
Alpha	Developer	Pete
Beta	Project Manager	Robert
Beta	Business Analyst	John
Beta	Developer	Frank
Beta	Developer	Bruce

As you can see, our projects often have multiple team members per role, and I'm trying list the appropriate names under a column for each role, one row per project:

Project	Project Manager	Business Analyst	Developer
Alpha	Will	John Amy	Sally Joe Pete
Beta	Robert	John	Frank Bruce

When I try to use a crosstab query in MS Access, I have to choose either the first or last name; I cannot find a way to list all of them.

When I try a pivot table in MS Excel, I can get each role to appear as a column, but the names still appear in the first column rather than within the column based on the team member's role.

A solution in either MS Access or MS Excel would meet my needs.

Thank you!

Answer 1

To do this in PQ, you have to

• group by Project
• group each sub-table by Role
• Combine the list of Team members using the LF character
• Pivot each subtable on the Role column

To use PQ if your original table was in Excel

• Select some cell in your original table
• Data => Get&Transform => From Table/Range or From within sheet
• When the PQ UI opens, navigate to Home => Advanced Editor
• Make note of the Table Name in Line 2 of the code.
• Replace the existing code with the M-Code below
• Change the table name in line 2 of the pasted code to your "real" table name
• Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps
• If you are loading this back to Excel, be sure to enable word-wrap on the cells with the line feeds in order to make display properly.

If your original table is from another source, you will need to access that source from PQ -- there are numerous connectors you can substitute for the first line in the code below

let
    Source = Excel.CurrentWorkbook(){[Name="TeamMembers"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Project", type text}, {"Role", type text}, {"Team Member", type text}}),

//Group by Project
//  Sub group by Role
//  Pivot on Role
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {
        {"roles", each Table.Pivot(
        Table.Group(_,"Role",{
            {"role", each Text.Combine([Team Member],"#(lf)")}
            }),List.Distinct([Role]),"Role","role")}
        }),

//Expand all columns
    roles = List.Distinct(#"Changed Type"[Role]),
    #"Expanded roles" = Table.ExpandTableColumn(#"Grouped Rows", "roles", roles,roles),

//set data types
    typeIt = Table.TransformColumnTypes(#"Expanded roles", List.Transform(Table.ColumnNames(#"Expanded roles"), each {_, type text}))
in
    typeIt

Answer 2

And for completeness here is an Access solution:

The problem (highlighted) is Access can only put 1 summary inside each cell of the cross-tab but there are multiple Team Members filling some roles (high-lighted). To get around this we recalculate TeamMember to be a rich text list like: Sally<br/>Joe<br/>Pete Fortunately there is already a function for doing this here: Microsoft Access condense multiple lines in a table

Public Function GetList(SQL As String _
                            , Optional ColumnDelimeter As String = ", " _
                            , Optional RowDelimeter As String = vbCrLf) As String
                           
' make sure to add a reference to the latest Microsoft ActiveX Data Objects x.x Library For me that was 6.1
Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String

On Error GoTo ProcErr

Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)
sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)
If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
    sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If

GetList = sResult
oRS.Close
oConn.Close

CleanUp:
    Set oRS = Nothing
    Set oConn = Nothing

Exit Function
ProcErr:
    ' insert error handler
    Resume CleanUp

End Function

The rich text format in access controls only works with the long text format. So start by converting Role and TeamMember to long text in the table. Unfortunately, GetList must return a string so we can't build the report directly from a query that uses GetList. Instead we build the report based on a similar query:

set the appropriate report controls to richtext then replace the reports(click on red) record source(make sure its reports record source that is changed) with the query that uses getlist. see http://allenbrowne.com/ser-63.html for a another work around involving a union query (memo= long text)

For the final query replace Team Member with a calculated field using GetList.

TeamMember: First(GetList("SELECT TeamMember FROM Sheet1 WHERE Project = '" & [Project] & "' AND Role = '" & [Role] & "'","<br/>","<br/>"))