Concatenate Duplicate Roles?? HELP!

[tjeee1993]

Hoping for a solution today! It is one of those things that is probably very obvious and I am just totally missing it. Here we go.

I work in a law office where we use a case management system to manage our workload. I export the data and use Access to create reporting for the entire office. One of the tables I update to weekly is my table called tbl_Connections. This is a table that let's me know who is assigned specific "Roles" for each case. And some of our cases have more than one Co-Leading Counsel, or more than one Paralegal, etc. The only thing different would be the connections actual name. It looks like this:

Matter Number | Role | LastName
16-07775 | Paralegal | Mask
16-07775 | Paralegal | Strong
16-09322 | Co-Counsel | Anders
16-09322 | Co-Counsel | Belk
16-09322 | Co-Counsel | Steel

Each week I send the attorney/paralegal an Excel worksheet that includes all of their active cases they are currently working on or assigned to. To do that I run a query that makes a table from the connections table and a master details table (where all the data for the case resides) and my criteria will look for their last name to export their specific report. My issue is that I cannot figure out how to combine the LastName from the tbl_Connections table where I have more than one attorney/paralegal in the same role, into the make table query for the excel worksheet.

I need to it show:
Matter Number | Role | LastName
16-07775 | Paralegal | Mask; Strong
16-09322 | Co-Counsel | Anders; Belk; Steel

I added the module from AllenBrowne ConcatRelated and when I try to run the results in a query I get the error:
Matter Number | Role | LastName | CombinedConnections
16-07775 | Paralegal | Mask | #Error
16-07775 | Paralegal | Strong | #Error
16-09322 | Co-Counsel | Anders | #Error
16-09322 | Co-Counsel | Belk | #Error
16-09322 | Co-Counsel | Steel | #Error

This is the SQL Statement I have set up after surfing here all day:

SELECT tbl_Connections.[Matter Number], tbl_Connections.LastName, tbl_Connections.Role, ConcatRelated([LastName],[tbl_Connections],"[Matter Number]=" & tbl_Connections.[Matter Number]) AS CombinedConnections
FROM tbl_Connections
WHERE (((tbl_Connections.[Matter Number]) In (SELECT [Matter Number] FROM [tbl_Connections] As Tmp GROUP BY [Matter Number],[Role] HAVING Count(*)>1 And [Role] = [tbl_Connections].[Role])) AND ((tbl_Connections.Ending) Is Null))
ORDER BY tbl_Connections.[Matter Number] DESC;


I am not an expert at this and am groveling for help from the masters here!

All that to just say HELP! Pretty Please! :banghead:

 

[pbaldy]

Yours looks like text so try

ConcatRelated([LastName],[tbl_Connections],"[Matter Number]='" & [Matter Number] & "'")

 

[tjeee1993]

no go. I get a pop up box "Enter Parameter value" for tbl_Connections???

 

[pbaldy]

Sorry, forgot they're all strings:

ConcatRelated("[LastName]","[tbl_Connections]","[Matter Number]='" & [Matter Number] & "'")

 

[tjeee1993]

oh wow, ok, I am sooooo close! ok, so in the CombinedConnections, it is combing EVERY name, and not just the ones that are in the same role. I thought I had it matching on Matter Number, and Role?

Here is the SQL STATEMENT:
SELECT tbl_Connections.[Matter Number], tbl_Connections.LastName, tbl_Connections.Role, ConcatRelated("[LastName]","[tbl_Connections]","[Matter Number]='" & [Matter Number] & "'") AS CombinedConnections
FROM tbl_Connections
WHERE (((tbl_Connections.[Matter Number]) In (SELECT [Matter Number] FROM [tbl_Connections] As Tmp GROUP BY [Matter Number],[Role] HAVING Count(*)>1 And [Role] = [tbl_Connections].[Role])) AND ((tbl_Connections.Ending) Is Null))
ORDER BY tbl_Connections.[Matter Number] DESC;

So, it should only return (for example the one I pasted below) Mask, Strong as they are the only two names found in the same Role for that matter Number. The other names listed are in different roles.
Matter Number | LastName | Role | CombinedConnections
16-07775 |Mask |Paralegal | Rozene, Turnip, Butler, Mask, Strong
16-07775 |Strong |Paralegal | Rozene, Turnip, Butler, Mask, Strong

What should I do different?

 

[pbaldy]

Can't read the example, but you have it matching on matter only. If you want both, try:

ConcatRelated("[LastName]","[tbl_Connections]","[Matter Number]='" & [Matter Number] & "' AND Role = '" & Role & "'")

 

[tjeee1993]

YES!!! That is it. Thank you so so so much! I wish I would have posted this yesterday to have this resolved before my quitting time. Looks like I will be staying a little late to clean this up and get it going!

Thanks so much again, ever grateful for you!

 

[pbaldy]

Happy to help!

 

[tjeee1993]

Paul! I may have marked this solved too soon. I have one more quick question.

We have a field called "Ending" in the tbl_Connections table. We add a date in that field when the assigned role ends because we re-assign it to someone else. So instead of have two paralegals, we would only have one if the Ending date has a value and two paralegals if it is null.

Do I add this to the ConcatRelated after the match on Role?
ConcatRelated("[LastName]","[tbl_Connections]","[Matter Number]='" & [Matter Number] & "' AND Role = '" & Role & "'")

I have WHERE in the statement at the end, like this:
WHERE (((tbl_Connections.Ending) Is Null));

Do I need to add that with the ConcatRelated? Sorry I am confused.

 

[pbaldy]

I don't really understand, but adding that to the function would look like:

ConcatRelated("[LastName]","[tbl_Connections]","[Matter Number]='" & [Matter Number] & "' AND Role = '" & Role & "' AND Ending Is Null")

 

[tjeee1993]

that worked thanks again.

 

[pbaldy]

Whew! No problem.

 

[tjeee1993]

hehe! I probably didn't asked right because I was in a panic for just a second.

So, it really slowed everything down in the database for the "Update" query to the New CombinedConnections. Is that normal?

 

[pbaldy]

I wouldn't be surprised by that slowing down a query. Basically for every record returned by the query, you're running a second query plus the little code loop. With big tables, that can slow the query way down.

 

[tjeee1993]

I wonder if I can shorten the "updating" every week to only updating records that have been modified? ok, now I am confusing you again probably. Let me see if I can make sense.

every week I export the connections data from the system (to import into the database), I only import the records that have a "Modified On Date" on or after the last update. So sometimes I only have 20 that actually got updated. But today, since you solved my issues on combining the duplicate role names, (thank you again!), it is slow because it is all my records, about 29,070 records actually.

SOOOO, in your opinion, is it possible to only update records using the modified date on or after in my criteria for the update query? Do you think that would make it faster? After these all update, I will test it out and see what it does. I am curious.

hmmm....

 

[pbaldy]

I would expect the query to run much faster when you restrict the records returned. Internally the criteria should be applied first, so the concat function would only run against the 20 records, not the 29k.

 

[tjeee1993]

ok, great. do you mind if I keep this thread open and give it a try so I can let you know the results or if I still have issues?

 

[pbaldy]

Sure, no problem.