How to Insert Aggregated Data from One MS Access Table to Another?

Moving Data Between MS Access Tables: An Aggregation Example

Data warehousing in MS Access often requires moving and transforming data from one table to another. This process can be efficiently handled using a SQL query.

Illustrative Query:

Let's say you need to extract and modify data from Table1 and insert the results into Table2. The following query accomplishes this:

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

Detailed Explanation:

• INSERT INTO Table2 (LongIntColumn2, CurrencyColumn2): This designates the destination table (Table2) and the specific columns (LongIntColumn2, CurrencyColumn2) to receive the data.
• SELECT LongIntColumn1, Avg(CurrencyColumn) AS CurrencyColumn1 FROM Table1: This part selects data from Table1. Avg(CurrencyColumn) calculates the average of the CurrencyColumn values.
• GROUP BY LongIntColumn1: This groups the results based on unique values in LongIntColumn1, ensuring the average is calculated for each distinct group.

This approach avoids the syntax error of previous attempts by correctly using the INSERT INTO ... SELECT structure without unnecessary VALUES keywords and parentheses.

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