title: Insert query results into table in ms access 2010
body: <p>I need to transform data from one schema into another in an MS Access database.  This involves writing queries to select data from the old schema and then inserting the results of the queries into tables in the new schema.  The below is an example of what I am trying to do.  The SELECT component of the below works fine, but the INSERT component does not work.  Can someone show me how to fix the below so that it effectively inserts the results of the SELECT statement into the destination table?  </p>
  
  <pre><code>INSERT INTO CompaniesTable 
      (CompanyName) 
      VALUES
      (
          SELECT DISTINCT 
              IIF(a.FIRM_NAME IS NULL, b.SUBACCOUNT_COMPANY_NAME, a.FIRM_NAME) AS CompanyName   
          FROM 
              (SELECT ContactID, FIRM_NAME, SUBACCOUNT_COMPANY_NAME FROM qrySummaryData) AS a
              LEFT JOIN 
              (SELECT ContactID, FIRM_NAME, SUBACCOUNT_COMPANY_NAME FROM qrySummaryData) AS b
              ON a.ContactID = b.ContactID
      );  
  </code></pre>
  
  <p>The definition of the target table (CompaniesTable) is:  </p>
  
  <pre><code>CompanyID      Autonumber  
  CompanyName    Text  
  Description    Text  
  WebSite        Text  
  Email          Text  
  TypeNumber     Number  
  </code></pre>