The ACC Update Query Based on Totals Query Fails occurs when a user run an update query and at such moment user unable to perform this update operation successfully and receives an error message on the PC screen. The update equerry is a powerful feature of the RDBMS(Relational Data Base Management) which can change the set of database record according to requirement and this can modify a huge amount of records at a time. This update equerry improves the performance of your application which becomes easy to maintain to a user. If you are getting an error message of the updatable query in this section you will able to know how to solve ACC: Update Query Based on Totals Query Fails
Symptom of ACC: Update Query Based on Totals Query Fails
When a user run an update query receives the below-given error message Operation must use an updatable query
Cause of the Update Query Based on Totals Query Fails
This error encounters due to two row of fields are in either a crosstab query or in a select query and subquery which contain the total functions and when this error occurs it shows that the total records of query unable to update. To solve this issue you have to process one record at a time and use domain function to calculate the update to values.
How to solve ACC: Update Query Based on Totals Query Fails
Process one record at a time By this steps you will able to know how to add a new value to an existing value Open MS Access and open the sample database NWIND.MDB then open the product table in the design view and add new field which is sales and save it and close the table. Now create a query based on the product and order details table because this two table will be joined in the product ID field. Now select update from the Query menu and drag the sales from the product table to query gid. Then you have to enter the below-given command in the Update To row of the Sales So Far column Remove 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]))
Now you can run the query and after that, your “2813 row will be updated” then click on ok button to finish this operation.
Use a domain function to calculate the update to value
Before applying this steps you can back up of your NWIND.MDB file because it may modify your sample database file. So you can perform this operation on a copy of the NWIND database file. Start MS Access and open NWIND.MDB and open the product table in design view then add a new field called Sales So Far to the table and give the data type currency and save then close it. Now create a new query based on Products table then select Update from the Query menu
Remove 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])
Now Run the query then you will able to see a received message which Shows that “77 row(s) will be updated.” Now close the operation. This information will be written to the Products table. If you are unable to apply this manual steps don’t worry you can take the help of a System technician or you may apply a third party tool to solve ACC: Update Query Based on Totals Query Fails.