2

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Silentdarkness
  • 461
  • 1
  • 10
  • 30
  • 1
    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 Jan 27 '14 at 12:23

1 Answers1

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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418