Concat Help

[crunchy]

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:

PID	Contract #	Pre-Tender	Awarded
PR123456	H1234	10,000	10,000
PR987654	H1234	20,000	20,000

This is what I want it to look like:

PID	Contract #	Pre-Tender	Awarded
PR123456, PR987654	H1234	30,000	30,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.

[#2]

@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