title: Concatenating multiple rows into single line in MS Access
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/92698/combine-rows-in-access-2007">Combine rows in Access 2007</a><br>
    <a href="https://stackoverflow.com/questions/7600637/access-2007-concatenate-fields-from-one-column-in-one-table-into-a-single-com">Access 2007 - Concatenate fields from one column in one table into a single, comma delmited value in another table</a>  </p>
  </blockquote>
  
  
  
  <p>Currently I have a table structure that is somewhat like this:  </p>
  
  <p><strong>Name</strong> --- <strong>Cat</strong> --- <strong>Desc</strong> --- <strong>Thresh</strong> --- <strong>Perc</strong> --- <strong>Err</strong> --- <strong>BP</strong><br>
  Bob -------C1-------Inf--------7Per--------0.05------0-----ADC2<br>
  Bob -------C1-------Inf--------7Per--------0.05------2-----BAC2<br>
  Bob -------C1-------Inf--------7Per--------0.05------0-----RBE2<br>
  Bob -------C1-------Inf--------7Per--------0.05------8-----VBE2<br>
  Bob -------C1-------Inf--------7Per--------0.05------6-----AEC2<br>
  Bob -------C1-------Inf--------7Per--------0.05------0-----PBC2<br>
  Bob -------C2-------Com------8Per--------0.45------1-----XBC4<br>
  Bob -------C2-------Com------8Per--------0.45------0-----AEC2<br>
  Bob -------C2-------Com------8Per--------0.45------0-----PBC2<br>
  Bob -------C2-------Com------8Per--------0.45------3-----ADC2<br>
  Bob -------C2-------Com------8Per--------0.45------0-----ADC2<br>
  Bob -------C2-------Com------8Per--------0.45------0-----BAC2<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----PBC2<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----ZTM2<br>
  Joe--------C1-------Inf---------7Per--------0.05------2-----QYC2<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----FLC2<br>
  Joe--------C1-------Inf---------7Per--------0.05------1-----KSC2<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----JYC2  </p>
  
  <p>What i'm looking to do is have 1 line per "Name" and per "Cat", that will sum up all the "Err" (per "Name" and "Cat") and concatenate only the "BP" fields into a single line. Such as:  </p>
  
  <p><strong>Name</strong> --- <strong>Cat</strong> --- <strong>Desc</strong> --- <strong>Thresh</strong> --- <strong>Perc</strong> --- <strong>Err</strong> --- <strong>BP</strong><br>
  Bob -------C1-------Inf--------7Per--------0.05-----16-----BAC2, VBE2, AEC2<br>
  Bob -------C2------Com------8Per--------0.45------4------XBC4, ADC2<br>
  Joe--------C1-------Inf--------7Per--------0.05------3------QYC2, KSC2  </p>
  
  <p>There have been similar questions asked but I cannot seem to apply it as my knowledge of VBA scripting is beginner. Is there any way to do all of this via SQL? If VBA scripting is the only option (ie. creating a function), any help would be greatly appreciated. Thank You in advance.</p>
  
  <p>Question part 2:<br>
  I created the function as per Allen Browne's guide. The module is saved as modConcatRelated. Now, i've tried to run this query (im not sure if this is the correct SQL to get the result that i'm looking for):  </p>
  
  <pre><code>SELECT
      [Name],
      [Cat],
      [Desc],
      [Thresh],
      [Perc],
      sum([Err]),
      ConcatRelated("[BP]", "make_table_bp", "[Err] = " &amp; [BP])
  FROM make_table_bp
  GROUP BY
      [Name],
      [Cat],
      [Desc],
      [Thresh],
      [Perc],
      [Err],
      [BP];  
  </code></pre>
  
  <p>It said "Error 3061. Too few parameters. Expected 1." Also it said "Undefined Function ConcatRelated." I'm looking for guidance on how to create the correct SQL statement so that I can call the ConcatRelated function correctly and yield the result as depicted above. Thanks again.</p>
  
  <p><strong><em>Next question:</em></strong><br>
  What if the table had a unique date field tagged on as the last column in the table. Something like this:  </p>
  
  <p><strong>Name</strong> --- <strong>Cat</strong> --- <strong>Desc</strong> --- <strong>Thresh</strong> --- <strong>Perc</strong> --- <strong>Err</strong> --- <strong>BP</strong> --- <strong>Date</strong>  </p>
  
  <p>Bob -------C1-------Inf--------7Per--------0.05------0-----ADC2--12/02/2011<br>
  Bob -------C1-------Inf--------7Per--------0.05------2-----BAC2--09/05/2011<br>
  Bob -------C1-------Inf--------7Per--------0.05------0-----RBE2--11/02/2011<br>
  Bob -------C1-------Inf--------7Per--------0.05------8-----VBE2--08/14/2012<br>
  Bob -------C1-------Inf--------7Per--------0.05------6-----AEC2--02/25/2009<br>
  Bob -------C1-------Inf--------7Per--------0.05------0-----PBC2--07/02/2011<br>
  Bob -------C2-------Com------8Per--------0.45------1-----XBC4--09/05/2011<br>
  Bob -------C2-------Com------8Per--------0.45------0-----AEC2--02/02/2010<br>
  Bob -------C2-------Com------8Per--------0.45------0-----PBC2--08/14/2012<br>
  Bob -------C2-------Com------8Per--------0.45------3-----ADC2--05/05/2001<br>
  Bob -------C2-------Com------8Per--------0.45------0-----ADC2--08/02/2010<br>
  Bob -------C2-------Com------8Per--------0.45------0-----BAC2--06/17/2010<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----PBC2--08/14/2012<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----ZTM2--09/05/2011<br>
  Joe--------C1-------Inf---------7Per--------0.05------2-----QYC2--05/17/2010<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----FLC2--3/19/2010<br>
  Joe--------C1-------Inf---------7Per--------0.05------1-----KSC2--09/05/2011<br>
  Joe--------C1-------Inf---------7Per--------0.05------0-----JYC2--08/14/2012  </p>
  
  <p>Let's say I wanted to build a query to say something like: show me all records still within this same format:  </p>
  
  <p><strong>Name</strong> --- <strong>Cat</strong> --- <strong>Desc</strong> --- <strong>Thresh</strong> --- <strong>Perc</strong> --- <strong>Err</strong> --- <strong>BP</strong><br>
  Bob -------C1-------Inf--------7Per--------0.05-----16-----BAC2, VBE2, AEC2<br>
  Bob -------C2------Com------8Per--------0.45------4------XBC4, ADC2<br>
  Joe--------C1-------Inf--------7Per--------0.05------3------QYC2, KSC2 </p>
  
  <p><strong>But</strong> for a date range of 01/01/2009 to 09/31/2011  </p>
  
  <p>@HansUp could you help with this?</p>