Insert query results into table in ms access 2010

Posted by CodeMed on Stack Overflow See other posts from Stack Overflow or by CodeMed
Published on 2013-10-25T23:23:50Z Indexed on 2013/10/26 3:54 UTC
Read the original article Hit count: 260

Filed under:
|

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?

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
    );  

The definition of the target table (CompaniesTable) is:

CompanyID      Autonumber  
CompanyName    Text  
Description    Text  
WebSite        Text  
Email          Text  
TypeNumber     Number  

© Stack Overflow or respective owner

Related posts about sql

Related posts about ms-access