title: Concatenate rows in Access based on two tables
body: <p>I'd like to aggregate group membership based on location, date and group number into a single field with spaces as delimiters. </p>
  
  <p>Table INDSIGHT:</p>
  
  <pre><code>    GROUP_IND | DATE_IND | STUDY_SITE | Subarea   
      01           9/9/2001   SDP          Shum
      01           9/9/2001   KOD          North 
      01           9/10/2001  SDP          Shum
      02           9/10/2001  SDP          Shum
      01           9/10/2001  KOD          East
  </code></pre>
  
  <p>Table IMAGES has:</p>
  
  <pre><code>GROUP_IMG | DATE    | STUDY_SITE | INDIVIDUAL
  01          9/9/2001  SDP           003
  01          9/9/2001  SDP           004
  01          9/9/2001  KOD           A16
  01          9/9/2001  KOD           019
  01          9/9/2001  KOD           021
  01          9/10/2001 SDP           003
  02          9/10/2001 SDP           006
  </code></pre>
  
  <p>What I want is:</p>
  
  <pre><code>GROUP | DATE    | STUDY_SITE | Subarea | Individuals
  01      9/9/2001  SDP           Shum      003   004
  01      9/9/2001  KOD           North     A16   019   021
  </code></pre>
  
  <p>I have implemented the following code using Allen Browne's <a href="http://allenbrowne.com/func-concat.html" rel="nofollow">ConcatRelated</a> function.</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
      i.GROUP_IND,
      i.DATE_IND,
      i.STUDY_SITE,
      i.Subarea,
      ConcatRelated(
          "INDIVIDUAL",
          "IMAGES",
          "GROUP_IMG = '" &amp; [GROUP_IND] &amp; "'"
           and "DATE = '" &amp; [DATE_IND] &amp; "'"
           and "STUDY_SITE = '" &amp; [STUDY_SITE]"'"
          ) AS Individuals
  FROM INDSIGHT AS i;
  </code></pre>
  
  <p>That query runs without error, but its results are not correct. The results are listing every individual, not just individuals for specific groups.</p>
  
  <p>Note the DATE fields are Date/Time and GROUP fields are text.</p>