Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL problem moving query result to table

    I'm working in VBA/SQL, but this is more of a SQL problem. I'm trying to move the result of an Access query to an existing table. The SQL for the query is:
    strSQL = "SELECT tblSEIDtoExt_Num.SEID, Sum(tblSEIDtoExt_Num.ReadyTime) AS SumOfReadyTime " _
    & "FROM tblSEIDtoExt_Num " _
    & "GROUP BY tblSEIDtoExt_Num.SEID;"

    Now I'm trying to move that result to an existing table with the following stmt:
    strSQL = "UPDATE tblSEIDtoExt_Num " _
    & "SELECT Sum(tblSEIDtoExt_Num.ReadyTime) AS SumOfReadyTime " _
    & "FROM qryTotalRdyTm " _
    & "SET tblSEIDtoExt_Num.TotalRdyTm = qryTotalRdyTm.SumOfReadyTime " _
    & "WHERE SEID= '" & strSEID & "';"
    db.execute strSQL

    I'm getting a syntax error. Any suggestinos as to how to resolve this will be greatly appreciated. Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Try changing: & "WHERE SEID= '" & strSEID & "';"
    To: & "WHERE SEID= " & Chr(34) & strSEID & Chr(34) & ";"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You can't use a SELECT statement "inside" an UPDATE statement, unless you use it in a FROM Clause (actually in a join in Access) and treat the SELECT statement as a derived table.
    However, at first look, I am not sure that situation will apply.

    How many records does qryTotalRdyTm return? If it is a single value, I suggest you execute the query independently, store the result in a variable and then use that variable value to in the UPDATE statement. It it returns more then a single value, let us know.
    Last edited by ruirib; 2011-09-21 at 19:50.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Gave your problem a better look and I think this SQL should work:

    Code:
    strSQL = "UPDATE tblSEIDtoExt_Num T INNER JOIN qryTotalRdyTm Q ON T.SEID = Q.SEID " & _
             "SET T.TotalRdyTm = Q.SumOfReadyTime " & _
             "WHERE  SEID= '" & strSEID & "';"
    This will work if the database is Access. If it is SQL Server, the SQL will need to be different.
    Last edited by ruirib; 2011-09-21 at 19:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •