title: Concatenating multiple rows, with multiple values in it, into single line in MS Access
body: <p>I am trying to create simple requirements management database. Basically I have 2 tables like below:</p>
  
  <p>Contract_requirements with 2 columns:</p>
  
  <pre><code>CR_ReqID    |   Description
  reqCR1      |   Contract req description 1
  reqCR2      |   Contract req description 2
  </code></pre>
  
  <p>SW_requirements</p>
  
  <pre><code>Title               |   SW_ReqID     |  RootReq
  SW req description 1|   reqSW1       |   reqCR1, reqCR2
  SW req description 2|   reqSW2       |   reqCR1
  SW req description 3|   reqSW3       |   reqCR2
  </code></pre>
  
  <p>And I would like to write query to receive such a table:</p>
  
  <pre><code>CR_ReqID  |Description                  |where used?
  reqCR1    |Contract req description 1   |reqSW1, reqSW2  
  reqCR2    |Contract req description 2   |reqSW1, reqSW3
  </code></pre>
  
  <p>Tables "Contract requirements" and "SW requirements" are in relation via column "RootReq"</p>
  
  <p>Ive tried to implement code from Allen Browne 
  <a href="http://allenbrowne.com/func-concat.html#Top" rel="nofollow noreferrer">http://allenbrowne.com/func-concat.html#Top</a></p>
  
  <p>This is my query </p>
  
  <pre><code>SELECT Contract_requirements.CR_ReqID, ConcatRelated("SW_ReqID     ","SW_requirements","RootReq = """ &amp; [CR_ReqID] &amp; """") AS Expr1
  FROM Contract_requirements;
  </code></pre>
  
  <p>but I get error in Access </p>
  
  <blockquote>
    <p>"Error3831: The multi-valued field 'RootReq' cannot be used in a WHERE or HAVING clause"</p>
  </blockquote>
  
  <p>Could you guys help me to make this working?
  Thanks in advance</p>