Results 1 to 4 of 4
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: MS Acces Update qurey

    I have created the following update query but MS Access has rejected to run the query as it is "Updateable qurey"

    Code:
    UPDATE Table1 SET Table1.Total = (SELECT Sum(item) AS total
    FROM table2
    WHERE (((table2.ID)='123456')););
    Can any one help me with this qurey?

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Do you only have one record in Table1? Or do you want the field Total updated in ALL records with the sum of item from Table2 where ID = 123456? Is ID really a text field? Is there actually a matching ID in Table1? Is there a little more detail you could give us?
    Thanks,

  3. #3
    Join Date
    May 2007
    Posts
    2
    The result of the select statement
    Code:
    (SELECT Sum(item) AS total
    FROM table2)
    is one value which the sum of the
    column item. One record in table1 should be upated. ID is a text field and there is a matching in ID table1. When I replace the select statement with a value the query works correctly. To me, the problem relies in the select statement. The syntax of the query seems to be fine.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Been there; had the same problem. I solved it by first running the sub query and storing the data in a real access table. Then I ran the update query using the table directly.

    It seems to have something to do with using a totals query for the sub query; access doesn't like doing that when making an update query. Don't know why, but it works for me.

    Sam

Posting Permissions

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