MS Access equivalent of FOR XML PATH in T-SQL to list related values [duplicate]

Question

I Have a table as follows in access:

MatterNO    Description     Amount
FL069509    SMS             R 50.00
FL069509    Call            R 52.00
FL069509    Summons         R 52.00
FL069509    Email           R 5 420.00
FL069509    Letter          R 0.12
NA076353"   SMs             R 123.10
NA076353"   call            R 0.53
NA076353"   summons         R 53.00
NA076353"   email           R 453.00
NA076353"   letter          R 4 530.00
NA076359    Call            R 50.00

I would like to group the MatterNO's and sum up the Amounts, but next to the amount in a separate column id like to display a list of all the descriptions that make up that amount.

For Example:

MatterNO    Amount    CSV
FL069509    R5574.12  SMS, Call, Summons, Email, Letter

I know in SQL theres a function called FOR XML PATH

Which looks as below:

SELECT t.MatterNo,SUM(Amount), STUFF(
(SELECT ',' + s.Descriptions
FROM Table1 s
WHERE s.MatterNo = t.MatterNo
FOR XML PATH('')),1,1,'') AS CSV
FROM Table1 AS t
GROUP BY t.MatterNo

Is it possible to do this in access?

Edit:

I added this from the comment below with ConcatRelated()

SELECT DISTINCT table1.MatterNO, Sum(table1.Amount) AS SumOfAmount,
ConcatRelated("Description","table1","MatterNo = '" & [MatterNo] & "'") AS Descriptions
FROM table1
GROUP BY table1.MatterNO, table1.Description;

And i get what i need.

However one problem, it is not adding the amounts up, how can i fix this?

In T-SQL there is FOR XML PATH. T-SQL means SQL Server. MS Access and MS SQL Server are not compatible. It's not possible to do this with an SQL statement in Access. — Tomalak
– Tomalak, Commented Jan 27, 2014 at 12:23

Gord Thompson · Accepted Answer · 2014-01-27 13:36:14Z

0

Try this instead:

SELECT
    MatterNO,
    SumOfAmount,
    ConcatRelated("Description","table1","MatterNo = '" & [MatterNo] & "'") AS Descriptions
FROM
    (
        SELECT
            MatterNO,
            SUM(Amount) AS SumOfAmount
        FROM table1
        GROUP BY MatterNO
    ) AS subquery

edited Jan 27, 2014 at 13:36

answered Jan 27, 2014 at 13:17

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS Access equivalent of FOR XML PATH in T-SQL to list related values [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions