Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Concat Help

Copper Contributor



I'm getting errors when I'm trying to follow along with the SQL code to combine items into one row. I want to remove duplicate Contract Numbers and combine them as below:


This what I have:


PIDContract #Pre-TenderAwarded 


This is what I want it to look like:


PIDContract #Pre-TenderAwarded 
PR123456, PR987654H123430,00030,000


Explanation: Each contract number has multiple PID's in it. What I want to see is how much all of them totaled together is per each contract, rather than separated out. 


I created a query out of the table that I have, so summing the numbers isn't the issue. The problem is that I'm trying to combine the short text together in a list of sorts in one cell and resources that I've found on google is beyond what I can currently understand. Any help would be appreciated.

1 Reply


Maybe you found a solution in the meantime, otherwise this could maybe help.


Your request could be solved with a VBA-function (ConcatRelated) that's developed by Allen Browne. I attached a file with the function. If you import this as a module in your database, you should be able to use the ConcatRelated-function in a query like this:

SELECT ConcatRelated("PID","ContractDetails") AS PID, Contract AS [Contract#], SUM([Pre-Tender]) AS [Pre-Tender SubTotal], SUM(Awarded) AS [Awarded SubTotal]
FROM ContractDetails
GROUP BY Contract;


This is the query output:




I named the table ContractDetails, but you can replace it with yours. Note that you have to replace it in the call to ConcatRelated() as well.


Hope it works.


Best wishes,