Access Query/VBA to Group from two fields and Concatenate values

You have not provided enough data to make more tests. Yes, you included screenshots, but data to copy and paste just 3 records, so I worked with that

I replied your table like this (name of my table is Table1):

Then, I have a Query like this:

The SQL code for this query is:

SELECT DISTINCT Table1.MrID, FINAL_ASSIGNEES([mrid]) AS ASSIGNEES FROM Table1;

As you can see, this SQL code invokes an UDF coded in VBA named FINAL_ASSIGNEES. The code of this UDF is:

Public Function FINAL_ASSIGNEES(ByVal vThisMrID As Long) As String
Dim RST As Recordset
Dim SqlStr As String

SqlStr = "SELECT DISTINCT Table1.MrID, CONCATENATE_ASSIGNEE([MrID],[Team]) AS ASSIGNEES FROM Table1 " & _
    "WHERE Table1.MrID=" & vThisMrID & ";"

Set RST = Application.CurrentDb.OpenRecordset(SqlStr, 2, 4)

With RST
    If .EOF <> True And .BOF <> True Then
        .MoveLast
        .MoveFirst
        Do Until .EOF = True
            FINAL_ASSIGNEES = FINAL_ASSIGNEES & .Fields(1).Value & ". "
            .MoveNext
        Loop
            FINAL_ASSIGNEES = Left(FINAL_ASSIGNEES, Len(FINAL_ASSIGNEES) - 2) 'minus 2 to get rid of extra ". "
    End If

    Set RST = Nothing
End With


End Function

And yes, this VBA code calls a second UDF named CONCATENATE_ASSIGNEE. The code of this second UDF is:

Public Function CONCATENATE_ASSIGNEE(ByVal vMrID As Long, ByVal vTeam As String) As String
Dim MyRST As Recordset
Dim MySQL As String

MySQL = "SELECT Table1.Assignee FROM Table1 " & _
    "WHERE (((Table1.MrID)=" & vMrID & ") AND ((Table1.Team)='" & vTeam & "'));"

Set MyRST = Application.CurrentDb.OpenRecordset(MySQL, 2, 4)
DoEvents

With MyRST
    If .EOF <> True And .BOF <> True Then
        .MoveLast
        .MoveFirst

        Do Until .EOF = True

            If IsNull(.Fields(0)) = True Then
                CONCATENATE_ASSIGNEE = CONCATENATE_ASSIGNEE & "Unassigned" & ", "
            Else
                CONCATENATE_ASSIGNEE = CONCATENATE_ASSIGNEE & .Fields(0).Value & ", "
            End If

            .MoveNext
            DoEvents
        Loop

        CONCATENATE_ASSIGNEE = vTeam & ": " & Left(CONCATENATE_ASSIGNEE, Len(CONCATENATE_ASSIGNEE) - 2) 'minus 2 to get rid of the extra ", "
    End If
    Set MyRST = Nothing
End With



End Function

But this gets kind of what you are after. If you are working with big recordsets, probably it will take some time to make calculations. But at least you can adapt this to your needs.