title: When inserting data from a query to a table, does the query run for each record inserted?
body: <p>I'm inserting data problematically into tables. When I do this from another table, it's swift, only slowed very slightly if there are a lot of records. Even then, it's a matter of seconds.</p>
  
  <p>When I insert from a query to a table, it goes into minutes - roughly a minute for every 1,000 records inserted.</p>
  
  <p>The source query itself, when just run as a select query, takes maybe 1 - 2 seconds. Is the query running for every record that's inserted? I'd hoped that it would run once for the whole data set. Or is there something else that's causing the function to run so slowly when compared to inserting "flat" data from another table.</p>
  
  <p>The VBA I'm using is fairly innocuous:</p>
  
  <pre><code>CurrentDb.Execute "SELECT [Extra Value Concatenation].* _
  INTO [" &amp; strTableName &amp; "] FROM [Extra Value Concatenation];" 
  </code></pre>
  
  <p>and the source query is below - it uses <a href="http://allenbrowne.com/func-concat.html" rel="nofollow">Allen Browne's Concatenate function</a>.</p>
  
  <pre><code>SELECT [Extra Fields - Fee Protection Insurance Concatenate].ContactID,
  ConcatRelated('[Fee Protection Insurance]',
  '[Extra Fields - Fee Protection Insurance Concatenate]',
  'ContactID = ' &amp; [ContactID]) 
  AS [Fee Protection Insurance]
  FROM [Extra Fields - Fee Protection Insurance Concatenate];
  </code></pre>
  
  <p><strong>EDIT: In answer to Fionnuala's comment, but I couldn't format it properly in the comments.</strong></p>
  
  <p>Using fictional data, here's roughly what I want.</p>
  
  <p>T1 contains client records.</p>
  
  <pre><code>ContactID    Name
  1            Example Limited
  2            Another Company Limited
  </code></pre>
  
  <p>T2 contains extra fields. ContactID is there as a foreign key, and may be duplicated if multiple records are held.</p>
  
  <pre><code>ContactID    FieldValue
  1            Value 1
  1            Value 2
  2            Value 3
  2            Value 4
  2            Value 5
  </code></pre>
  
  <p>When I left join the tables, the duplicates from T2 show up, so I get </p>
  
  <pre><code>ContactID    Name                       FieldValue
  1            Example Limited            Value 1
  1            Example Limited            Value 2
  2            Another Company Limited    Value 3
  2            Another Company Limited    Value 4
  2            Another Company Limited    Value 5
  </code></pre>
  
  <p>when what I want is</p>
  
  <pre><code>ContactID    Name                       FieldValue
  1            Example Limited            Value 1; Value 2
  2            Another Company Limited    Value 3; Value 4; Value 5
  </code></pre>
  
  <p>Hence concatenating the data in a temporary table seemed like a good idea, but is slowing everything down. Is there another way I should be looking at my query?</p>