Insert to Access 2007 table with some values from other tables

I am using Access 2007 [normally SQL Server] I'm trying to insert records into a table whereby certain values are ID's from other tables. For example:

insert into table values ((select id from another_table), 1, 'Hello', etc)

This is possible in SQL Server.

I开发者_运维问答 get an error that says, Query must contain at least one table or something...

Anyone know the syntax for this in Access? I've tested all the selects and they all produce the expected results, but when inserted in the above type of statement, I get the error.

I don't want to extract too much data into memory, so I'd prefer to get the above to work, instead of loading the ID's and names as a collection of objects.

I am not exactly sure what you want, but perhaps you are looking at it from the wrong angle?

INSERT INTO Table (ID, FK_ID, F2)  
SELECT 1, T2_ID, "Hello"  
FROM  Table2 
WHERE ID=1