Results 1 to 5 of 5
  1. #1
    MrSmart is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Location
    Fife, Scotland
    Posts
    3

    SQL statements working separately, but not working when combined

    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):
    Code:
    INSERT INTO groomTypes ( typeOfGroom, cost )
    VALUES ("Special Cut", 60);
    Inner query that works fine:
    Code:
    SELECT [Most expensive]
    FROM [Q5a];
    Combine the queries and this won't work:
    Code:
    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:


    Click image for larger version. 

Name:	Q11 together they don't work.PNG 
Views:	9 
Size:	17.9 KB 
ID:	40751Click image for larger version. 

Name:	Q11 part b works.PNG 
Views:	9 
Size:	6.7 KB 
ID:	40752Click image for larger version. 

Name:	Q11 part a works.PNG 
Views:	9 
Size:	16.5 KB 
ID:	40753

    Thanks,
    Liam

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    try

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

  3. #3
    MrSmart is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Location
    Fife, Scotland
    Posts
    3
    Quote Originally Posted by Ajax View Post
    try

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

    Thanks for the response but that code appears invalid.

    Liam

  4. #4
    MrSmart is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Location
    Fife, Scotland
    Posts
    3
    Sorry Ajax, I just had to remove the last bracket.

    Thank you for your help.

    Liam

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sorry -left a bracket at the end

    Code:
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 25
    Last Post: 09-20-2018, 04:48 AM
  2. Replies: 9
    Last Post: 04-19-2017, 01:36 PM
  3. Working on an SQL Insert / Update combined
    By robsworld78 in forum Programming
    Replies: 0
    Last Post: 02-23-2012, 10:24 PM
  4. 2 Fields combined to textbox not working
    By kris335 in forum Forms
    Replies: 3
    Last Post: 09-19-2011, 10:32 AM
  5. Replies: 3
    Last Post: 10-13-2010, 03:35 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums