How to apply media query in table?

Microsoft Access 2007, SQL Query - How to insert values from another table?

• Would appreciate any help :) I have two tables in Access and I am trying to populate them. The first table is artist: name birth death nationality styles I have populated these fine. The next table is my paintings table: painting_id title media height width artist My SQL query at the moment is: INSERT INTO paintings VALUES ("P01", "The Opening of Waterloo Bridge", "Oil on canvas", 1308, 2180, SELECT name FROM artist WHERE name = "John Constable"; As you can see, all of the values apart from Artist will fit in as the name of the artist is from another table. Is there a way of adding it in? When creating the painting table I have added a reference to artist : artist VARCHAR(20) REFERENCES artist(name) Thanks in advance :)

• Answer:

  Two ways to do this - INSERT INTO Paintings VALUES ( "P01", "The Opening of Waterloo Bridge", "Oil on canvas", 1308, 2180, ( SELECT name FROM artist WHERE name = "John Constable" ) ) OR INSERT INTO Paintings SELECT "P01", "The Opening of Waterloo Bridge", "Oil on canvas", 1308, 2180,name FROM artist WHERE name = "John Constable" -- In 'thoughts'... Lonely Rogue.