SQL statements working separately, but not working when combined

[MrSmart]

Afternoon everyone,

I'm new to SQL but enjoying learning it and new to the forum. I'm a high school teacher in Scotland and working with databases is a relatively new part of the course. So to help my pupils learn SQL, I created a DB about dog grooming.

The assessment is finished, apart from the final INSERT task. This involves using an inner query/subquery. Both queries work separately. When I combine them I get the following error: "Query input must contain at least one table or query".

Pupils are asked to insert a new groom and make it £5 more expensive than the current most expensive groom. So eventually the query will be (SELECT [Most expensive] FROM [Q5a])) + 5, but for the sake of my request for help, I'm leaving the +£5 out just now.

Outer query that works fine (I've used the number 60 just to check it works):

INSERT INTO groomTypes ( typeOfGroom, cost )
VALUES ("Special Cut", 60);

Inner query that works fine:

SELECT [Most expensive]
FROM [Q5a];

Combine the queries and this won't work:

INSERT INTO groomTypes ( typeOfGroom, cost )
VALUES ("Special Cut", (SELECT [Most expensive] FROM [Q5a]));

Google hasn't helped, other than suggest this is an Access issue, not my SQL. But i thought I'd come and ask here in case I'm missing something simple.

Screenshots of me running the queries:


Thanks,
Liam

[CJ_London]

try

INSERT INTO groomTypes ( typeOfGroom, cost )
SELECT "Special Cut", [Most expensive] FROM [Q5a])

[MrSmart]

try

INSERT INTO groomTypes ( typeOfGroom, cost )
SELECT "Special Cut", [Most expensive] FROM [Q5a])

Hi Ajax,

Thanks for the response but that code appears invalid.

Liam

[MrSmart]

Sorry Ajax, I just had to remove the last bracket.

Thank you for your help.

Liam

[CJ_London]

sorry -left a bracket at the end

INSERT INTO groomTypes ( typeOfGroom, cost )
SELECT "Special Cut", [Most expensive] FROM [Q5a]

You can just create your select query then change it to an insert query