Forum Discussion

crunchy's avatar
crunchy
Copper Contributor
Jan 13, 2020

Concat Help

Hello,

 

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 
PR123456H123410,00010,000
PR987654H123420,00020,000

 

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.

  • Woldman's avatar
    Woldman
    Iron Contributor

    crunchy 

    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,

    Tieme

     

Resources