UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Query To Group And Concatenate Fields, Access 2013    
 
   
KDavidP1987
post Jun 10 2019, 04:23 PM
Post#1



Posts: 86
Joined: 12-June 18



Hello All,

I have a challenging problem I'm attempting to solve and could use your expertise in this matter.

I am attempting to replicate in Access 2013 queries for some reports on IT Tickets on a FootPrints Service Core 11.6 Database. I've completed the queries and calculations to replicate most fields from the front end reports, except that I need to find a way to group together and consolidate/concatenate fields associated with ticket assignees.

These assignees (people or groups tickets assigned to) are listed out separately within an assignees table of FootPrints Database (See Attached Screenshots). When the front end application generates reports it somehow groups together the individual and team assignee information in a particular way I'm unable to emulate (See screenshots Attached of Front-End report extract).

Note: I had to black out employee names for obvious reasons, the names are listed in the Indiv_Assignee field. I tried to gather examples of tickets matched across the screenshots shown data.

Assignees in front-end reports are separated by : , multiple team members are seperated by ,'s, and multiple teams are separated by .'s

Is there a way to mimic this process through the use of a query (or VBA if necessary) in Access?

Sincerely,

Kristopher
This post has been edited by KDavidP1987: Jun 10 2019, 04:25 PM
Attached File(s)
Attached File  Capture456.PNG ( 9.11K )Number of downloads: 6
Attached File  Capture345.PNG ( 10.79K )Number of downloads: 6
Attached File  Capture234.PNG ( 17.66K )Number of downloads: 5
Attached File  Capture123.PNG ( 29K )Number of downloads: 7
 
Go to the top of the page
 
theDBguy
post Jun 10 2019, 04:31 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure I follow but when talking about concatenating records, I usually point people to this one. Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 11 2019, 08:19 AM
Post#3



Posts: 86
Joined: 12-June 18



Hey DBGuy,

Great to hear from you again.

Apologies if the request is confusing! I need to combine all the assignees (individual and team assignees) within a single field, grouped by the ticket number (mrID) they associate with.

So, where there is the following in the database

MrID | Assignee | Team
12345 | Bob Smith | Help Desk Tier 1
12345 | Jane Smith | Help Desk Tier 1
12345 | (Null) | Telecom

It should appear as 1 field, like this:

MrID | Assignees
12345 | Help Desk Tier 1: Bob Smith, Jane Smith. Telecom:

Sincerely,
This post has been edited by KDavidP1987: Jun 11 2019, 08:21 AM
Go to the top of the page
 
theDBguy
post Jun 11 2019, 10:19 AM
Post#4


Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks for the clarification. It sounds more straightforward now. Have you tried the code I recommended earlier? You may have to do it in two steps.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 11 2019, 01:08 PM
Post#5



Posts: 86
Joined: 12-June 18



Hey again!

I have attempted your solution, to no avail at this time. It indicates the query is running (on a single months data), but never completes) I don't know if I have it formatted correctly, and I assume it's only built to concatenate one of the fields, and not both of them together.

CODE
SELECT DISTINCT qry_M3_Assignment_R2.MrID, qry_M3_Assignment_R2.Team_Assignee, SimpleCSV(  "SELECT [Indiv_Assignee] FROM
qry_M3_Assignment_R2 WHERE mrID=" & [mrID]  ) AS ConCatAssignee
FROM qry_M3_Assignment_R2;


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




I also received a response on StackOverflow's forum which seems to more closely match the desired result, but I was unable to get his solution working as well. If you would like to take a look I'm pasting a link below. It was about the same result, the query begins running, but never seems to end, even on a single months dataset.

https://stackoverflow.com/questions/5654513...catenate-values

If you happen to have any ideas that could resolve this I am ALL ears, because it's the last piece I need in replicating the front-end reports!!

Sincerely,
Kristopher Penland
Go to the top of the page
 
KDavidP1987
post Jun 11 2019, 01:21 PM
Post#6



Posts: 86
Joined: 12-June 18



Also, in case you need it, here is the adjusted code from the other Forum:

The Query:

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.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 & "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


Somehow the responder was able to make this work on a set of data he created in Access. Originally I received an error when attempting to run it until I added the DAO. to RecordSet declaration in variables.

Sincerely,
Go to the top of the page
 
KDavidP1987
post Jun 11 2019, 01:53 PM
Post#7



Posts: 86
Joined: 12-June 18



Want to provide a quick update:

The code from the other forum does work, but it took 30 minutes to complete a query of just 2 days tickets. Perhaps it can be adjusted somehow to improve efficiency/speed

Sincerely,

Kristopher
Go to the top of the page
 
theDBguy
post Jun 11 2019, 02:01 PM
Post#8


Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


Hi Kristopher. How many records are you processing? Are you able to post a sample db, so we could see if there's another solution?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 11 2019, 02:27 PM
Post#9



Posts: 86
Joined: 12-June 18



Hey,

Unfortunately, I am not able to post a sample DB due to PII, terribly sorry for the inconvenience.

I checked into the number of records earlier spanning 1/1/2018 - Now() and it was 344,000, appx. The query to gather all the records from the assignee's table (qry_M3_Assignment_R2) runs almost immediately. It seems the VBA necessary to group together and concatenate the assignees is what's slowing it down.

Sincerely,

Kris
Go to the top of the page
 
theDBguy
post Jun 11 2019, 02:31 PM
Post#10


Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


Hi. It would be nice if you could reproduce the db without PII data. Anyway, you might be able to speed it up if you could use a DISTINCT query with the regular table (with duplicates).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 13 2019, 01:02 PM
Post#11



Posts: 86
Joined: 12-June 18



Hey TheDBGuy,

Sincerest apologies for the delay!

After some finagling, I was able to get a sample of data (2 days worth, 2000 records) transferred into a pseudo-database. The names are fake, and random numbers are on the ends to maintain uniqueness amongst multiple assignees. The scripts are included, but some some odd reason they don't even run in this database. I keep getting a Mismatch error regarding this line of code...

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


I've verified my tool references are the same as in the original database. Perhaps you will have better luck than me!

Sincerely,
Kris
Attached File(s)
Attached File  PseudoDB.zip ( 108.31K )Number of downloads: 4
Attached File  ErrorCapture.PNG ( 9.83K )Number of downloads: 5
 
Go to the top of the page
 
ADezii
post Jun 13 2019, 07:19 PM
Post#12



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


Sorry for jumping in late, but I may have a viable solution which I will Post tomorrow. Unfortunately, it too is slow, but I'll Post it anyway later.
Go to the top of the page
 
ADezii
post Jun 14 2019, 09:11 AM
Post#13



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


Here is a Preliminary Demo that will hopefully provide most of what you are looking for. Basically, it uses a single Query with a Calculated Field along with a Public Function. It will process 2,000+ Records in 3.5 seconds, not earth shattering, but under the circumstances (Distinct Clause, Grouping, Concatenating, etc.), not bad. I won't go into the details as to how and why it works, but I'll simply Upload the Demo and let you make the decision as to its usefulness.
Attached File(s)
Attached File  PseudoDB_Revised.zip ( 95.55K )Number of downloads: 3
 
Go to the top of the page
 
KDavidP1987
post Jun 14 2019, 09:16 AM
Post#14



Posts: 86
Joined: 12-June 18



No worries, ADezii,

I'm open to any solutions, and GREATLY appreciate the help!! Do keep in mind that this is intended to run in reports covering 2 years worth of data, for reports. In relevance to table size, I would estimate it will be processing around 400K - 600K records on Assignment info, after all in only 2 days worth of data there were 2000 records.

Naturally, I don't mind it taking a few minutes to accomplish this, I'm honestly not expecting something that runs in the blink of an eye, but if it exceeds the timeframe to run these reports the traditional way then the solution is not viable as a replacement.

I look forward to your solution!

Sincerely,
Kris
Go to the top of the page
 
KDavidP1987
post Jun 14 2019, 09:17 AM
Post#15



Posts: 86
Joined: 12-June 18



Just saw your solution after posting my initial reply, ADezii, sincerest apologies for the lapse! I will take a look over your solution soon and get back with you. I may test it out on the main dataset to see how it runs if all goes well!

Thank you very much!!

Sincerely,
Kris
Go to the top of the page
 
ADezii
post Jun 14 2019, 09:38 AM
Post#16



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


In the meantime, I will run some Trials over larger Data Sets.
Go to the top of the page
 
KDavidP1987
post Jun 14 2019, 10:06 AM
Post#17



Posts: 86
Joined: 12-June 18



Hey Adezii and theDBGuy,

I'm going to resupply a larger sample dataset for your use. I also noticed a minor mistake I made in the creation of that fake data. CC is supposed to be used as the team name for assignment type 2 individual users, so they are grouped together. Type 1 individual users are suppposed to have the team name of Individual Users.

Also, as another forum user noted, because of the random number generator it is possible a fake name may be mentioned twice in the context of 1 ticket, because the random number was generated twice by the script.

Sincerest apologies for this lapse, I will have the necessary adjustments made shortly, hopefully!

Thank you both, very much, for your continued assistance!!

Sincerely,
Kris
Go to the top of the page
 
ADezii
post Jun 14 2019, 10:47 AM
Post#18



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


As a side note, [mrID] is Declared as a DOUBLE in your Table where it should be a LONG.
This post has been edited by ADezii: Jun 14 2019, 11:36 AM
Go to the top of the page
 
ADezii
post Jun 14 2019, 12:23 PM
Post#19



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


Well, I had some time at work so I ran some very basic, preliminary Timing Tests on the Code for a varying number of Records. Unfortunately, the results are not very impressive! frown.gif The only way I see this happening is to maybe change the Query to an Append Query, that is run on a weekly basis. Each week, the Results of that week would be added to a Master Table. See reference below:

RecordsProcessing Time
1000015.50 secs
500002.13 mins
10000012.84 mins
20000051.01 mins

P.S. - Didn't bother testing beyond 200,000 Records (mader no sense). shrug.gif
Go to the top of the page
 
KDavidP1987
post Jun 17 2019, 12:36 PM
Post#20



Posts: 86
Joined: 12-June 18



Oof, I'm sorry to see the method didn't work out with optimal results. If you have any other ideas I am open to them.

In case you wanted to run it with some proper data, I've created a larger dataset for the test. Unfortunately, I was unable to get a full year's worth of data, because of upload file size limitations for this DB, but was able to get through 30K

Note: The emails are now marked with CC: as the Team, and individual assignees should be marked with INDIVIDUAL USERS: as their team, so these two groups can be organized together properly.

Sincerely,
Kris
Attached File(s)
Attached File  qry_M3_Assignment_R2_Psudo.zip ( 1.04MB )Number of downloads: 1
 
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 07:35 PM