How to do INSERT into a table records extracted from another table

Question

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES

(SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);

I tried but get a syntax error message.

What would you do if you want to do this?

Answer 1

Don't use "VALUES", or parenthesis. 

Try below code:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)

SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;