SQL MsAccess embedding select into INSERT statement for some specific values

Question

I have two tables: Artist and Work.

Artist is a relation of painters with ArtistID, FirstName and LastName.

Work is a relation of their paintings with columns WorkID, Title and ArtistID.

Now, my artist relation has data and I am now inserting values to Work table. WorkID is a counter, so no problems there. I want to use something that does

INSERT INTO Work (Title, ArtistID)
VALUES('Toledo', SELECT ArtistID FROM Artist WHERE FirstName='Joan');

But it doesn't seem to work or accept the Select Statement as a part of insert. What should I do?

Answer 1

Try this

INSERT INTO Work (Title, ArtistID) 
SELECT 'Toledo', ArtistID FROM Artist WHERE FirstName='Joan'

Answer 2

This is an interesting question because both of the answers from Apostolos and from HansUp will work. However, they will behave differently if you have more than one [Artist] named 'Joan'.

In that case, Apostolos' method will insert multiple rows into [Work], while the approach taken by HansUp will only insert one row into [Work] based on the [ArtistID] returned by DLookup() (which will be the first match that it finds in the [Artist] table).

You'll have to decide which answer suits your purposes best. Be sure to Accept that answer, but also bear in mind that you can still upvote any answers that you find helpful.