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:
This is what I want it to look like:
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.
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.