Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (Access 2000)

    I get an error named "operation must use an updatable query" in an effort to build an update query from one query an one table.I want to use the result from the query to update a field in the table.Whats wrong with my query ?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    The queries qy1 and qry2 are Totals queries. Totals queries are not updatable because they group records and aggregate values. Any query based on a Totals query is not updatable either.

    Moreover, you have set the 'Update to' line for the number field items5 to the string "qry[diff]". Even if the query had been updatable, that wouldn't have worked.

    If the query worked, it would change items5 from 22 to 2 (the value from qry2) for productid = 73. Is that what you really want?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Yes thats what i want. In my example i want items5 from 22 to be changed to 2. Which is the result in qry3.Due to reasons you have explained Access does not accet my critera and puts them into commas. In principle i want the fields in items5 to be upated with the results obtained in query 3.I guess it is not possible. Is there any other way ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    The expression qry[diff] makes no sense anyway, since there is no query named qry and since there is no . between the query name and the field name.

    This is the query you need:

    UPDATE products SET items5 = DLookUp("diff","qry3","productid=" & [productid])
    WHERE Productid In (SELECT productid FROM qry3)

    It is based on products only, and it uses DLookup and a subquery based on qry3.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    And of course it works.Thanks a lot !

    your grateful Markus

Posting Permissions

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