How to Correctly Insert Transformed Data from One Table to Another in MS Access?

Insert converted data from one table to another in MS Access

When using MS Access data warehouse queries, you often need to extract and transform data from one table and then insert it into another table. The goal is to create a query that extracts specific data from the source table and then inserts this transformed data into the target table.

Syntactic issues with initial query

In the given query attempt:

<code class="language-sql">INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES
(SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);</code>

There is a syntax error related to the use of 'VALUES' and parentheses. In MS Access, the correct syntax for inserting data into a table using the SELECT statement is as follows:

<code class="language-sql">INSERT INTO 目标表 (列)
SELECT 值 FROM 源表;</code>

Corrected query

To resolve syntax issues, remove "VALUES" and brackets from the query:

<code class="language-sql">INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;</code>

This corrected query should successfully extract data from Table1, calculate the average of each LongIntColumn1's CurrencyColumn , and insert the transformed data into Table2.

The above is the detailed content of How to Correctly Insert Transformed Data from One Table to Another in MS Access?. For more information, please follow other related articles on the PHP Chinese website!