4 Attachment(s)
Group Two Fields and Concatenate
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).
Attachment 24376
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])
Attachment 24381
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:
Attachment 24378
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:
Code:
SELECT DISTINCT qry_M3_Assignment_R2.MrID, FINAL_ASSIGNEES([mrID]) AS ASSIGNEES
FROM qry_M3_Assignment_R2;
UDF # 1
Code:
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:
Code:
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
