Microsoft KB Archive/116142

= ACC: Update Query Based on Totals Query Fails =

Article ID: 116142

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q116142



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.

 Start Microsoft Access and open the sample database NWIND.MDB. Open the Products table in Design view. Add a new field called Sales So Far with a Currency data type to the table. Save and then close the table. Create a new query based on the Products and Order Details tables. The two tables will be joined automatically on the Product ID field. From the Query menu, choose Update (or click the Update Query button on the toolbar). Drag the Sales So Far field from the Products table to the query grid.</li> 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]))

</li> Run the query. When you are prompted "2813 row(s) will be updated" choose OK.</li></ol>

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.

<ol> Start Microsoft Access and open the sample database NWIND.MDB.</li> Open the Products table in Design view.</li> Add a new field called Sales So Far to the table. Give the field a Currency data type. Save and then close the table.</li>  Create a new query based on the Order Details table. Add the following fields to the query:

<pre class="fixed_text">     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. </li> Create a new query based on the SalesSoFar Test and the Products tables.</li> Add the Sales So Far field (from the Products table) to the query grid.</li> From the Query menu, choose Update.</li> In the Update To field in the Sales So Far column, enter the following line:

[SumPerProduct] </li> 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.</li></ol>

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.

<ol> Start Microsoft Access and open the sample database NWIND.MDB.</li> Open the Products table in Design view.</li> Add a new field called Sales So Far to the table. Give the field a Currency data type. Save and then close the table.</li>  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.

<pre class="fixed_text">     Field: Sales So Far Update To: DSum("[Quantity]*[Unit Price]","Order _                   Details","[Product ID]=" & [Product ID]) </li> 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.</li></ol>

<div class="status_section">

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.

<div class="moreinformation_section">

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.

<ol> <li>Start Microsoft Access and open the sample database NWIND.MDB.</li> <li>Open the Products table in Design view.</li> <li>Add a new field called Sales So Far to the table. Give the field a Currency data type. Save and then close the table.</li> <li> Create a new query based on the Order Details table. Add the following fields to the query, and then save the query as Query1:

<pre class="fixed_text">     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. </li> <li>Create a new query based on Query1 and the Products table.</li> <li>Add the Sales So Far field (from the Products table) to the query grid.</li> <li>From the Query menu, choose Update.</li> <li>In the Update to field in the Sales So Far column, enter the following line:

[SumPerProduct] </li> <li>Run the query. You will receive the error message stated above.</li></ol>

<div class="references_section">