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 I missing? I think I might need to use 'INNER JOIN' somehow but I'm still learning the basics of SQL and can't wrap my head around this particular problem and why Access is throwing the error that it does.

Answer 1

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).

Answer 2

You can substitute a DLookup expression for your SELECT role_num ... query.

SELECT role_num FROM role_index WHERE role_name='Follow Up'
DLookup("role_num", "role_index", "role_name='Follow Up'")

Use that DLookup in your INSERT.

INSERT INTO master_eod
    (
        sha1_hex,
        [role]
    )
VALUES
    (
        'ef03ff03',
        DLookup("role_num", "role_index", "role_name='Follow Up'")
    );

I enclosed role in square brackets because it is a reserved word.