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:

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:

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!

CodePudding user response：

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 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

CodePudding user response：

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:

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

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