title: MS Access equivalent of FOR XML PATH in T-SQL to list related values
body: <p>I Have a table as follows in access:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>For Example:</p>
  
  <pre><code>MatterNO    Amount    CSV
  FL069509    R5574.12  SMS, Call, Summons, Email, Letter
  </code></pre>
  
  <p>I know in SQL theres a function called FOR XML PATH</p>
  
  <p>Which looks as below:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Is it possible to do this in access?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I added this from the comment below with ConcatRelated()</p>
  
  <pre><code>SELECT DISTINCT table1.MatterNO, Sum(table1.Amount) AS SumOfAmount,
  ConcatRelated("Description","table1","MatterNo = '" &amp; [MatterNo] &amp; "'") AS Descriptions
  FROM table1
  GROUP BY table1.MatterNO, table1.Description;
  </code></pre>
  
  <p>And i get what i need.</p>
  
  <p>However one problem, it is not adding the amounts up, how can i fix this?</p>