Challenging Problem - Group Two Fields and Concatenate

[KDavidP1987]

Hi all,

This is my first time posting to this forum for help! To start, I'll describe the situation, and then the problem.

Program: Access 2013

Database Schema:
FootPrints ServiceCore 11.6
https://communities.bmc.com/servlet/...ta%20Model.pdf
Table: 13) Issue Assignment

• Master1_Assignment
• AssignmentType
• Master1

Background:
I am trying to replicate front-end reports pulled from a FootPrints ServiceCore 11.6 Database, to improve efficiency. This system, FootPrints, is what our company utilizes for managing IT tickets (Help Desk, networking, etc). The footprints front-end reporting system is Extremely finicky, sometimes it crashes while trying to pull reports and other times it takes 5 - 10 minutes to pull, and you can only do a month of data at a time. So far I have been able to replicate many of the calculated fields found in the CSV reports pulled from the front-end, except for the formatting of 1 key field.

The Problem:
Among the various fields shown in the reports, one of the key fields I need to replicate is Assignees. This field indicates who the ticket is assigned to within IT and the FootPrints system; 1 ticket can have multiple assignees. In the FootPrints database Assignment information is stored in an Assignment table, and split up for each individual assignee (Shown below).

FPSCAssignmentTbl.jpg

This is how it looks as shown from the database, approximately. (Apologies for censorship, the data contains employee PII I had to black out [Names and IDs])

AssigneeTableView2.jpg

As you can see in the examples above (hopefully), the format of the final product is [Team Name]: [Employee Name], [Employee Name]. [Team #2 Name] [CC: Email Addresses]

Team names are followed by :'s, Employee names from each team are separated by ,'s, and multiple teams are separated by .'s. Finally, any individuals not part of a team are put at the end with CC: in front of their email addresses. The CC: portion is taken care of by my query, so that part can be ignored. I just need a way to group together and concatenate.

When assignment is pulled through the front-end system it comes out looking like this in the reports:

AssigneeTableView1.jpg

I need an efficient way to replicate this format for assignee in reports I pull through Access. The separate assignment info needs to be grouped together and concatenated to follow the format above.

This was able to be completed, to an extent, through VBA.... Which runs perfectly, but takes 15 minutes to process 1 days worth of tickets (500 - 1000 tickets, appx). I need a method that will emulate the result of the following VBA, but more efficiently.

Query SQL:

SELECT DISTINCT qry_M3_Assignment_R2.MrID, FINAL_ASSIGNEES([mrID]) AS ASSIGNEES
FROM qry_M3_Assignment_R2;

UDF # 1

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


SqlStr = "SELECT DISTINCT qry_M3_Assignment_R2.MrID, CONCATENATE_ASSIGNEE([MrID],[Team_Assignee]) AS ASSIGNEES FROM qry_M3_Assignment_R2 " & _
    "WHERE qry_M3_Assignment_R2.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

UDF # 2:

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


MySQL = "SELECT qry_M3_Assignment_R2.Indiv_Assignee FROM qry_M3_Assignment_R2 " & _
    "WHERE (((qry_M3_Assignment_R2.MrID)=" & vMrID & ") AND ((qry_M3_Assignment_R2.Team_Assignee)='" & 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 & "" & ", "
            Else
                CONCATENATE_ASSIGNEE = CONCATENATE_ASSIGNEE & .Fields(0).Value & ", "
            End If


            .MoveNext
            DoEvents
        Loop


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

ANY help you can provide which would lead to a solution for this frustrating challenge would be most appreciated!

Link to previous Forum: (For reference)
https://stackoverflow.com/questions/...atenate-values

The user who helped me to generate this code indicated that pulling the recordsets is what is causing the bottleneck (see comments), and thus the approach above is not sufficient.

Sincerely,
Kris