ACC: Update Query Based on Totals Query Fails (1.x/2.0)

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run an update query, you receive the following error message:

   Operation must use an updatable query.

CAUSE

This error message occurs when the values in the query's Update To row are fields in either a crosstab query or a select query or subquery that contains aggregate (totals) functions. The error message indicates that the records in the totaling query cannot be updated.

RESOLUTION

There are three methods of working around this behavior. The first method processes one record at a time, instead of using a totaling query. The second method uses a temporary, or intermediate, table to hold the results of the totaling query. The third method uses a domain function instead of a totaling query.

Method 1: Processing One Record at a Time

The following example demonstrates how to add a new value to an existing value and place the result back into a field.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. Open the Products table in Design view.

3. Add a new field called Sales So Far with a Currency data type to the table. Save and then close the table.

4. Create a new query based on the Products and Order Details tables. The two tables will be joined automatically on the Product ID field.

5. From the Query menu, choose Update (or click the Update Query button on the toolbar).

6. Drag the Sales So Far field from the Products table to the query grid.

7. In the Update To row of the Sales So Far column, enter the following expression.

   NOTE: In the following sample expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.

         IIf(IsNull([Sales So Far]),[Order Details].[Unit Price]*_
         [Order Details].[Quantity],[Sales So Far]+_
         ([Order Details].[Unit Price]*[Order Details].[Quantity]))
   

8. Run the query. When you are prompted "2813 row(s) will be updated" choose OK.

Method 2: Using a Temporary Table

This method stores the results of the totaling query in an intermediate table, which is then used in the update query. The following example demonstrates this method.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. Open the Products table in Design view.

3. Add a new field called Sales So Far to the table. Give the field a Currency data type. Save and then close the table.

4. Create a new query based on the Order Details table. Add the following fields to the query:

         Field: Product ID
            Total: Group By
   
         Field: SumPerProduct: [Unit Price]*[Quantity]
            Total: Sum
   
      NOTE: To see the Total field, click the Totals button on the toolbar, or
      choose Totals from the View menu.
   
      From the Query menu, choose Make Table. In the Make New Table box,
      type "SalesSoFar Test" (without quotation marks). Save the query as
      Query1, and then run the query.
   
   

5. Create a new query based on the SalesSoFar Test and the Products tables.

6. Add the Sales So Far field (from the Products table) to the query grid.

7. From the Query menu, choose Update.

8. In the Update To field in the Sales So Far column, enter the following line:

         [SumPerProduct]
   

9. Run the query. You will receive a message stating "77 row(s) will be updated." Choose OK. The SumPerProduct information will be written to the Sales So Far field in the Products table.

Method 3: Using a Domain Function to Calculate the Update To Values

This method uses a domain function instead of a totaling query and does not require the use of an intermediate table. This method may take longer than the second method if the table being totaled is large. The following example demonstrates this method.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. Open the Products table in Design view.

3. Add a new field called Sales So Far to the table. Give the field a Currency data type. Save and then close the table.

4. Create a new query based on the Products table, and then choose Update from the Query menu. Add the following field to the query.

   NOTE: In the following sample field, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this field.

         Field: Sales So Far
            Update To: DSum("[Quantity]*[Unit Price]","Order _
                       Details","[Product ID]=" & [Product ID])
   
   

5. Run the query. You will receive a message stating "77 row(s) will be updated." Choose OK. The information will be written to the Products table.

STATUS

This behavior is a design limitation. Changes to this behavior are being reviewed and will be considered for inclusion in a future release of Microsoft Access.

MORE INFORMATION

Steps to Reproduce Behavior

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. Open the Products table in Design view.

3. Add a new field called Sales So Far to the table. Give the field a Currency data type. Save and then close the table.

4. Create a new query based on the Order Details table. Add the following fields to the query, and then save the query as Query1:

         Field: Product ID
            Total: Group By
   
         Field: SumPerProduct: [Unit Price]*[Quantity]
            Total: Sum
   
      NOTE: To see the Total field, click the Totals button on the toolbar, or
      choose Totals from the View menu.
   
      This query creates a list of all the products sold (grouped by the
      Product ID), and the total dollar amount from those sales.
   
   

5. Create a new query based on Query1 and the Products table.

6. Add the Sales So Far field (from the Products table) to the query grid.

7. From the Query menu, choose Update.

8. In the Update to field in the Sales So Far column, enter the following line:

         [SumPerProduct]
   

9. Run the query. You will receive the error message stated above.

REFERENCES

Microsoft Access "User's Guide," version 2.0, Chapter 13, "Changing Data with Action Queries," pages 311-312

For more information about update queries that fail, search for "Updating Underlying Tables," using the Microsoft Access Help menu.

For additional information about using domain functions, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q103403
   TITLE     : ACC: Custom Domain Functions Similar to DFirst() and DLast()

   ARTICLE-ID: Q108098
   TITLE     : ACC1x: DLookup() Usage, Examples, and Troubleshooting Tips