• Update query based on another query

    Author
    Topic
    #460575

    I am trying to update fields in my tables. I made an update query and updated thousands of records using data drawn from another table. Then I needed to update another field in the same table, but this time the data is in a query. I get “Operation must use an updateable query” every time I try to run the update query. I can change the update query to a select query and it runs with no errors. The query that the update query is drawing from is not updateable, but I am not trying to update the query, I am trying to update a table. If the data is there, why won’t it just write it to the table? I have a report that prints this out every day, but I need to start saving it in the table for historical analysis.

    Any ideas?

    Viewing 1 reply thread
    Author
    Replies
    • #1165226

      The select query that an update query is based on needs to be updateable, otherwise it’s not clear to which records the updates are going to be applied. You may be able to change the design of the query so that it becomes updateable, but if you want help with that we’d need to see a stripped down and zipped copy of the database.

      • #1165235

        Clearly, that’s what the error message is saying, but try as I might to understand it; it just makes no sense to me. It is not an ambiguous query, if it was, I would not be able to display the results on my report. If the query is valid, why can I not save the data to a table? Making the original select query updateable is not a likely option, it is a complicated query based on choosing the closest value from another table via an underlying group-by query.

        For today, I worked around this by changing from an update query to a make table query. Then, I ran an update query based upon the new table, presto, my data is updated like I wanted. I need a long term solution, something I can run every day from now on. I can automate the process, build a macro to delete the temporary table, run the make table query, and then run the update query, tie this macro to the On Close event on the data entry form. I had a process like that in the past, but it was prone to problems when multiple users would inadvertently try to use and delete the same temporary data tables at the same time, so I told myself I would never do that again. Would you advise this approach? If I used code instead of the query builder in Access03, is there a way to update the records without first writing them to an intermediate temporary table?

        Thanks for your help!

        The select query that an update query is based on needs to be updateable, otherwise it’s not clear to which records the updates are going to be applied. You may be able to change the design of the query so that it becomes updateable, but if you want help with that we’d need to see a stripped down and zipped copy of the database.

        • #1165237

          Without seeing the design of the query, it’s hard to provide assistance.

          A common cause for a query not being updateable is a join between tables where there isn’t a unique index on the link fields on either side. If that is the case, see if it’s possible to create a unique index on the “one” side of the join.

          Sometimes, you can get around this as follows:
          – Open the query in design view.
          – Click in an empty part of the upper half of the query design window.
          – Activate the Properties window.
          – Set the Unique Records property to Yes. This corresponds to adding DISTINCTROW to the SQL statement of the query.

        • #1165247

          In addition to Hans’ comments about updatability, if your query joins three or more tables, or if you have calculated values in your query (it appears you do), then the query will not be updateable. And easy test is to check and see if you can append new records. If you can’t then the query is not updatable. You can also check to see if you can start an update to a specific record, and it won’t let you if the query isn’t updateable.

          Based on what you’ve said, I would be inclined to develop a VBA module that opens the appropriate recordsets and does the updates in a fashion somewhat similar to the Make Table approach you are using. The advantage of VBA over using macros and such is that you can create error handling routines to deal with the weird things. Even in those cases, the recordset you are updating must however be updatable of course. In summary, it is possible to do it in code, and not have to create a temporary table. If you would like some assistance with that we can try to point you in the right direction.

    • #1175978

      Following with interest….because I have same error reporting, so if I may.
      one original table with data:
      tbl_PO_Summary
      Client_Ref – text
      Purchase_Order – text
      Tax_Year – number
      Tax_Period – number
      BillAmount – number

      A query built on sql db table derives the current sum bill amount as posted in separate bill fields. Because the number of postings are many, then the billamount is summed over the grouped period
      qry_PO_Details

      The query and the table are brought together in an update query (but same error of “Operation must use an updateable query” occurs)

      SQL for the update query is:
      UPDATE DISTINCTROW qry_PO_Details INNER JOIN tbl_PO_Summary ON (qry_PO_Details.Tax_Period = tbl_PO_Summary.Tax_Period) AND (qry_PO_Details.Tax_Year = tbl_PO_Summary.Tax_Year) AND (qry_PO_Details.Purchase_Order = tbl_PO_Summary.Purchase_Order) AND (qry_PO_Details.Client_Ref = tbl_PO_Summary.Client_Ref) SET tbl_PO_Summary.BillAmount = ([qry_PO_Details].[billamount]);

      If needed I’ll post new thread but seeing as it is practically identical error then I figured would be OK to post.
      Any help much appreciated.
      Thanks
      Alan

      Cheshire, cloudy and wet.

      • #1175987


        SQL for the update query is:
        UPDATE DISTINCTROW qry_PO_Details INNER JOIN tbl_PO_Summary ON (qry_PO_Details.Tax_Period = tbl_PO_Summary.Tax_Period) AND (qry_PO_Details.Tax_Year = tbl_PO_Summary.Tax_Year) AND (qry_PO_Details.Purchase_Order = tbl_PO_Summary.Purchase_Order) AND (qry_PO_Details.Client_Ref = tbl_PO_Summary.Client_Ref) SET tbl_PO_Summary.BillAmount = ([qry_PO_Details].[billamount]);

        You don’t indicate what version of Access you are using, which may or may not have any bearing on the issue, but in general the more recent the version, the more picky the database engine is about what queries are updateable, and what are not. You could try setting the property of the query called Recordset Type to Dynaset (Inconsistent Updates), but I would be more inclined to make a temporary table as the original poster did, or take the VBA approach if you have a multi-user situation.

        • #1176017

          You don’t indicate what version of Access you are using, which may or may not have any bearing on the issue, but in general the more recent the version, the more picky the database engine is about what queries are updateable, and what are not. You could try setting the property of the query called Recordset Type to Dynaset (Inconsistent Updates), but I would be more inclined to make a temporary table as the original poster did, or take the VBA approach if you have a multi-user situation.

          Apologies, it’s access 2007 office sp2
          Again, with multi-users the temp table is fraught with sharing issues.

          • #1176031

            Union query!!
            Blinking blast!!!!!
            The originating source query (parent query?) was a union query which is a no-no for use in the update query tree (as are grouped queries involving summed fields).
            resolved the union query with a select query calculated field and all updates nicely now.

            Thanks for the suggestions and hopefully this will give pointers to other ‘updaters’.
            Cheers
            Alan

            • #1176053

              Glad you were able to solve it – the possibility of it being a union query completely escaped me.

    Viewing 1 reply thread
    Reply To: Update query based on another query

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: