How to INSERT INTO with more than one SELECT statement.

[Addy75]

Hey everyone, I just wondered if this is possible. My code will say it all

INSERT INTO DVD(DVD_ID,Title,Description,Rating_ID)
SELECT '111','Rambo','Stallone', 
Rating_ID FROM Rating WHERE Rating ='R'

The code above works but I need to get data from two other tables and the following code does not work:

INSERT INTO DVD(DVD_ID,Title,Description,Rating_ID,Format_ID,Category_ID)
SELECT '111','Rambo','Stallone', 
Rating_ID FROM Rating WHERE Rating ='R',
Format_ID FROM Format WHERE Format ='DVD',
Category_ID FROM Category WHERE Category ='Action_Adventure'

I get an error of: "Number of query values and destination fields are not the same."

Any alternative for this or do I have to create two INSERT statements? Any help will be appreciated. Thank you

[amrut]

Create > query , Select your table and change the query to APPEND query. You will have the correct syntax.

[Addy75]

Thank you for your response. So it did convert my statement into this when I include just one of my outside tables:

INSERT INTO DVD ( DVD_ID, Title, Description, Rating_ID )
SELECT '111' AS Expr1, 'Rambo' AS Expr2, 'Stallone' AS Expr3, Rating.Rating_ID
FROM Rating
WHERE (((Rating.[Rating])='R'));

It says I'm about to append 1 row and when I click yes I get this message:
Attachment 19038

[Addy75]

They are all varchar(20) so I don't understand the conversion failure.