Using INSERT INTO with 'SELECT' to supply some values but not others (Access 2010)

Question

I have two tables that look like this: ``` ('MASTER_EOD' TABLE) ID SHA1_HEX ROLE ---------------------------- 1 ff34bb03 2 2 eef0a005 1 ('ROLE_INDEX' TABLE) ROLE_NUM ROLE_NAME -------------------------- 1 Welcome Calls 2 Follow Up 3 Outbound ``` It should be noted that `ROLE` is joined to `ROLE_NUM` in the `ROLE_INDEX` table. I am trying to write an amend query where I can pass a string from `ROLE_NAME` (such as 'Follow Up', 'Outbound') etc. and it's associated `ROLE_NUM` will be put into the '`ROLE`' column. I've tried to accomplish this with the following query, as a test; ``` INSERT INTO master_eod ( sha1_hex, role ) VALUES ('ef03ff03',(SELECT role_num FROM role_index WHERE role_name='Follow Up')); ``` But I get the following error: Query input must contain at least one table or query. However, if I put the `SELECT role_num FROM role_index WHERE role_name='Follow Up'` into it's own query, it works.. Likewise, if I the whole SELECT part with a raw value such as 3, it works. What am…

Accepted Answer

Try doing it in the most standard way in SQL, by using the `SELECT` as the whole source in the `INSERT` instead of just a single column sub-query:

```
INSERT INTO master_eod ( sha1_hex, role )
SELECT 'ef03ff03', role_num FROM role_index WHERE role_name='Follow Up' ;
```

Note that you must ensure that such a query (the `SELECT` alone) returns just a single row, otherwise you would end up inserting many rows at once in a single go, one for each role_num found (which may or may not be desirable).

Using INSERT INTO with 'SELECT' to supply some values but not others (Access 2010)

Solution

Problem