Results 1 to 7 of 7
  1. #1
    adamtate94 is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2016
    Posts
    7

    INSERT INTO - Can't have subquery return multiple values

    I am trying to insert into a table which has two columns, CertificateID and ElementID, I am wanting to insert multiple rows for CertificateID (Me.CertificateID) and multiple rows of ElementID.
    I am using :
    "INSERT INTO dbo.TrainingCertificateElements (CertificateID,ElementID) VALUES (" & Me.CertificateID & ",(select ElementID from CourseElementsDefault WHERE CourseID LIKE '" & Me.CourseID & "'))"



    It knows how many ElementIDs to insert as it is returning many in the sub query, however my VB code does not not know how many CertificateID's to insert and I am just using 'Me.CertificateID'

    I am getting the error : "SubQuery returned more than 1 value, this is not permitted when the subquery follows =, !=,<,<=,>,> = or when the subquery is used as an expression"

    Any ideas?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I do not see your SQL working that way. It seems you want to UPDATE multiple records that match the criteria with the value of your current record's CertificateID. If that is the case, perhaps an UPDATE statement is more appropriate.
    http://www.w3schools.com/sql/sql_update.asp

  3. #3
    adamtate94 is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2016
    Posts
    7
    Hi thanks for the reply.

    The table that I am wanting to insert into is blank at the moment so I definitely want to insert. My problem is that the subquery is trying to insert multiple values for the ElementID but the CertificateId is only one value, which is Me.CertificateID, on my currenty form. I need the certificateID to be inserted as many times as the elementID is inserted.
    The table looks like this

    CertElementID(IDENTITY 1,1) PRIMARY KEY || CertificateID || ElementID
    1 ... 44 1
    2... 44 2
    3... 44 3


    etc

    Thanks

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I see. I suppose your subquery that has the criteria could join on the same table and criteria your form is using. It is likely to be more efficient to use DAO to retrieve your subquery and then append a record for each iteration. With each iteration, you can use your variable, CertificateID

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The left side of your query implies there is an equal with the right side, hence the message about =, !=,<,<=,>,> =. If it were a select query with this problem, it is possible to use the IN clause to deal with multiple values on the subquery side, but I don't know if that's possible with an append query. I don't know where I'd put the IN operator between the two queries, and I doubt it would be WHERE CourseID IN, since that locates it in the subquery side. Even if it was correct, I'm fairly certain you will not get it to work in conjunction with the LIKE operator. Another possibility for dealing with record sets that comes to mind is HAVING as in
    (select ElementID from CourseElementsDefault Group By CourseID Having CourseID LIKE '" & Me.CourseID & "'))". I've been cutting and pasting here, without really checking the syntax with respect to quotes and such, so take care with that.

    You could play with these ideas, but I'd do it on test tables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    adamtate94 is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2016
    Posts
    7
    Hi guys thanks for the replies, I have solved the problem by simply putting the CertificateID in the Select clause so it inserts this every time there is a moduleid matched:
    strSQL = "INSERT INTO dbo.TrainingCertificateElements (CertificateID,ElementID) (select '" & Me.CertificateID & "',ElementID from CourseElementsDefault WHERE CourseID LIKE '" & Me.CourseID & "')"

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    OK, that makes sense. Being in the same table it was easy to include. However, this hints of duplication of data.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-04-2016, 11:31 AM
  2. Subquery Inside Insert query statement
    By Sitakanta1989 in forum Access
    Replies: 4
    Last Post: 05-05-2015, 12:05 AM
  3. Insert Multiple Checkbox Values to one Textbox
    By dshillington in forum Programming
    Replies: 1
    Last Post: 12-28-2011, 10:10 AM
  4. INSERT INTO ... SELECT Subquery
    By TheDeceived in forum Programming
    Replies: 1
    Last Post: 10-19-2010, 09:29 AM
  5. Replies: 4
    Last Post: 09-03-2010, 02:55 PM

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