Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 25

Thread: Challenging Problem - Group Two Fields and Concatenate

  1. #1

    Question Challenging Problem - 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).



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



    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:



    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
    Attached Files Attached Files
    Last edited by KDavidP1987; 06-12-2019 at 08:54 AM. Reason: Enlarged Pictures

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    The time taken to process 500-1000 records is very odd, I have VBA processing 1000s of records in around 1 to 2 minutes.
    Can you please provide some dummy data for me to work with, Excel 2007 format or an Access 2007 format table would be best for me as I only have those versions.

  3. #3
    Hey OBP,

    Thank you for getting back with me regarding this inquiry! Yes, it runs very slowly for only a days worth of records. Over the two year span I need to extract it would be covering about 350,000 - 450K records.

    I'm still working on some weekly reports at the moment, but will look into extracting some data tomorrow for you to use. Unfortunately, I can only provide an extract if no PII is included, which would mean that names and emails may include aliases, or they will be excluded.

    I look forward to talking with you more tomorrow!

    Sincerely,
    Kristopher

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    Is the table in Access?
    Is where you have the word Null actually a blank field?
    Last edited by OBP; 06-13-2019 at 06:52 AM.

  5. #5
    Hey OBP,

    Sorry for the delay, it has been a busy morning!

    After some finagling, I was able to get a pseudo database created with 2 days worth of ticket data (2000 records) and the scripts. I replaced all names with fakes, of course, Added random numbers to them to maintain uniqueness for multiple assignees. However, for some reason, I get a error when attempting to run them that I do not get in the original database, regarding this line of code:

    ErrorCapture.jpg

    Set RST = Application.CurrentDb.OpenRecordset(SqlStr, 2, 4)
    I checked my tools -> references library to ensure I have the same items checked, and they are. Perhaps you will have better luck than me with this.

    The source database is a SQL Server Database, I was granted access to it through an ODBC connection for this project. My queries are all in Access 2013.

    There are some NULL values in the table/query, as there are situations where a ticket is assigned to a team, but no individual on that team, or vice versa in some cases. There are also tickets with NO assignee, that are taken care of by first level (Tier 1) support staff, without additional work required.

    Sincerely,
    Kris
    Attached Files Attached Files

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    OK, I will take a look at the table and see what I can do.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    OK, I have looked at the data, can you tell me what is the common field in the table.
    It was the MrID in the data that you originally put on line, is it the same in this one?
    What do you do with the records that only have one instance of a MrID?
    Also do you process all the records, ie Open & Closed ones?
    And what about were all other fields are Null?
    Last edited by OBP; 06-13-2019 at 12:21 PM.

  8. #8
    I don't know exactly what you mean by common field (Primary Key?), apologies. the mrID (which is the ticket number) is the field which assignees should be grouped by. Some records may only have one instance of an MrID, if there is only 1 assignee, or if the ticket was not assigned. In those cases the script puts "Unassigned" in the field.

            If CONCATENATE_ASSIGNEE <> "" Then
            Else
                CONCATENATE_ASSIGNEE = "Unassigned"
            End If
    Yes, all records are processed for reporting. That is to say, all statuses are included.

    Hope this answers your questions!

    Sincerely,
    Kris

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    OK, I think I have it and yes it was the MrID that I will group on.

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    Here is a first attempt, I am not sure that I have the Assignees and team quite right yet, but it will give you an idea of what it does.
    The database opens with a form called Form1 which has a Command Button to run the VBA.
    The VBA deletes the records in a Table called Report Output.
    It then opens a Recordset based on the data from your table in a query that orders the data by MrID.
    It then loops through the records and concatenates the data and writes it to the Table called Report Output.
    Let me know if this is basically what you want and I will tweak the Asignees and Teams etc.
    You may have to set the VBA Editor's Library References to suit your Access 2013.
    Attached Files Attached Files

  11. #11
    That is just Brilliant, OBP! I love how fast it runs!

    Unfortunately, the ordering is not quite right, but this is still a HUGE step in the right direction!! I appreciate all the work you put into this, little did I realize you would go through and completely redo the VBA to use a different, clearly more efficient, method. I can't wait to get it tuned to concatenate properly and use it on the 2 year span of data, and then in the larger weekly/monthly reports.

    For some reason the sort order is all over the place, I see what you were saying now. The team should go first, followed by a :, and the agents on that team (separated by commas), then a period between each team. Any users CC'd (email addresses) should go to the end of assignee. Perhaps this could be accomplished through different ordering and some form of looping.

    Is it possible to make this type of script run off a single query, or does it have to be completed through a form? The reason I ask is because it will represent 1 field in a larger set of data that makes up the reports. It would be easiest if it didn't have to be updated separately from when the main report is ran, as an extra step. I assume the precautions of verifying data deletion cannot be bypassed in Access?

    I will take a more thorough look through your scripts tomorrow!

    Thank you once again, sir; you are a gentleman and a scholar!!

    Sincerely,
    Kris Penland

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    Sorry about the formatting, I forgot the ":" completely.
    I did it this way deliberately to put the data in to a temporary Table. You can now create a query using the table or just add it directly to your main Query linked by MrID.
    Yes the messages for data deletion can be switched off, I left them on so that you could see that they were deleted.
    I will look at improving the way the data is stored in the CONCATENATE_ASSIGNEE field.

    Now I have a concern about why the original code took so long to run, it may be that it was embedded in the query or it may be to do with the ODBC connection. So to run a years worth of data it might actually be better to import the data in to a table in Access, run the report and then delete the data.
    I know it is not the format that you want but it might be an idea to try running it on your query connecting to the SQL table to see how long it takes to run.

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    I am not sure that I understand about the CCs as I have seen no mention of them, do they go on the end of the Assignees for a Team or at the end of the field?

    Also what do you do with records like 1879037 where the Team for the first 4 records are Null and then you have team Data Owners - Credit Cards - ACH PCI and team Apps - Finance.
    So do you have : CC: Person5@Contoso.com, CC: Person5@Contoso.com, CC: Person6@Contoso.com
    Note CC: Person5@Contoso.com features twice, is that likely to happen?

    I think I need an example of each variation you are likely to meet.
    Last edited by OBP; 06-14-2019 at 07:40 AM.

  14. #14
    Hey OBP,

    Great questions, apologies if I missed anything in describing the outputs!

    So in the reporting scheme there are team assignments and individual assignments. Among the individual assignments there are two types. The first type is individual users who assigned outside their team (not proper method, but the system allows it). These are identified under the team name "Individual Users:" followed by the individuals names. The second type are individuals who are CC'd as assignees on the ticket, and contains the email addresses. These are identified as team name "CC:" followed by the individuals email addresses!

  15. #15
    When I went to recreate the data I forgot to add in an Iff structure for these individual users that are not emails, which should be categorized under the "team name" as "Individual Users".

    The structure (order) of assignees is therefore:

    [Team Name]: [Employee Name], [Employee2 Name]. [Team2 Name]: . [Individual Users:] [Individual1], [Individual2]. [CC:] [Email Address1], [Email Address2]...

    In writing this out I also realized I put CC: into the pseudo database as a portion of the individual user's name, it is supposed to go into the team ID for type 2 individual users. I will correct this when I get a free moment. Sincerest Apologies!

    Regarding your question about the individual showing up twice, that is purely a result of the random number generator I used to replace real names and email addresses. IN reality those two two different email addresses, they just received the same number in the roulette table of randomness between 1 and 6.

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    That is good info, so will you post another table?
    In your example the Individual Users get a tag in front saying "Individual Users"?
    And the email addresses go with those individual users, it wasn't obvious in the the first table?

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    I have had another look at the other data and by ordering the AssignmenttypeID first, then the Team_Assignee shows the order of things much better and should make the data concatenation easier.
    Last edited by OBP; 06-14-2019 at 10:10 AM.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    This version looks better.
    Attached Files Attached Files

  19. #19

    RE: Revised TEST Data

    Hey OBP,

    Apologies for the delay, this is the first chance I've had to correct the pseudo data and get out.

    I've fixed the identifications of CC and INDIVIDUAL USERS values within the data, with those two groups now showing as team names, so they can be included in the grouping and concatenation process.

    I've attached a larger table of values for your tests.

    Sincerely,
    Kris
    Attached Files Attached Files

  20. #20
    Also, I forgot to note, I had to remove a significant amount of data from the initial extract because of the 1 MB limit to uploads on this DB. I think there are about 30K rows worth in it, maybe a little less.

    Sincerely,
    Kris

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •