CODEWITHSUNDEEP
sqlms-access
CodeMed
CodeMed

Reputation: 9191

Insert query results into table in ms access 2010

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

Upvotes: 1

Views: 11988

Answers (1)

Hogan
Hogan

Reputation: 70513

Maybe you have a typo but joining a table to itself you don't need the join, this should work fine. With an insert on a select statement you don't use the values keyword.

INSERT INTO CompaniesTable (CompanyName,Description,Website,Email,TypeNumber) 
    SELECT DISTINCT IIF(FIRM_NAME IS NULL, SUBACCOUNT_COMPANY_NAME, FIRM_NAME), 
                    '','','',0 
    FROM qrySummaryData

Upvotes: 5

Related Questions