An Update Query is an action query (SQL statement) that changes a set of records according to criteria (search conditions) you specify. It's a very powerful feature and a fundamental part of relational databases since you can modify a huge number of records at one time. Understanding and using Update Queries improves the performance of your applications (versus doing the same changes manually or in code), and makes them easier to maintain.
From the Access designer, you can interactively create a query and specify its type:
Update Query Option when Designing Queries in MS Access 2013 and 2016
Update Query Option when Designing Queries in MS Access 2007 and 2010
Update Queries let you modify the values of a field or fields in a table. You can specify the records to modify by applying a filter (WHERE clause) and linking the table to other tables and queries.
The updated value can be:
UPDATE table [join] SET newvalue WHERE criteria
The UPDATE statement has these parts:
|table||The name of the table containing the data to modify|
|join||JOIN clause if linking to another table(s) to specify which records are modified|
|newvalue||An expression for the value to be inserted into a particular field in the updated records|
|criteria||An expression that determines which records are updated. Only records that satisfy the expression are updated.|
UPDATE is useful when you want to change many records or when the records that you want to change are in multiple tables.
You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the US:
UPDATE tblOrders SET [OrderAmount] = [OrderAmount] * 1.1, [Freight] = [Freight] * 1.03 WHERE [ShipCountryRegion] = 'US'
There are several ways to use Update Queries:
In this example, all invoices with a balance due that are 30 days late have the LateStatus field updated to True.
UPDATE tblInvoices SET [LateStatus] = True WHERE (([Balance] > 0) AND ([InvoiceDate] < Date()-30)
In this example, a date field is emptied by setting it to Null:
UPDATE tblTasks SET [DueDate] = Null
In this example, the Contacted field is incremented by one if the record has Called = True. Each contact may have a different value in its Contacted field depending on how many times it was contacted in the past.
UPDATE tblContact SET [Contacted] = [Contacted] + 1 WHERE [Called]
In this example, an employee's FirstDay field is set to the same value as the HireDate.
UPDATE tblEmployee SET [FirstDay] = [HireDate]
In this example, the invoice's GrandTotal field becomes the sum of its Total and Tax fields.
UPDATE tblInvoices SET [GrandTotal] = [Total] + [Tax]
In this example, the address records are updated with the City and State value from a zip code lookup table. If the record has a zip code that matches the lookup table, its City and State fields are replaced.
UPDATE tblAddress INNER JOIN tblZipCodes ON [tblAddress].[ZipCode] = [tblZipCodes].[ZipCode] SET [tblAddress].[City] = [tblZipCodes].[City], [tblAddress].[State] = [tblZipCodes].[State] WHERE ([Country] = 'US')
Any built-in VBA function that returns a value can be used as the update value. In this example, the Country field is converted to all upper case using the VBA UCase command.
UPDATE tblAddress SET [Country] = UCase([Country]) WHERE ([Country] Is Not Null)
Note: This doesn't work if you're in an ADP since those queries run on SQL Server which doesn't know VBA syntax.
Any public function that you create in a standard module can be used in a query. Your field is updated with the return value of the function. If your function takes parameters, you can pass field values as parameters and the function executes for each record that is processed.
UPDATE tblAddress SET [MyField] = MyCoolFunction([Field1], [Field2])
Note: This doesn't work if you're in an ADP since those queries run on SQL Server which can't "see" your modules or functions.
If an Update Query fails to modify the table's data, the first thing to verify is that the underlying table is updateable. Simply open the table and manually try to edit the field. If you can't do it manually, the query can't make the change either. This can be due to several reasons:
Assuming you can edit your table, your query may fail and display this error message when you run it:
If so, check out our paper on Error 3073: Operation must use an updatable query: Dealing with Non-Updateable Queries and the Use of Temporary Tables in Microsoft Access which discusses some of the issues and workarounds.
Make sure you spend the time to understand how to create and use Update Queries in MS Access. They are extremely powerful and eliminate a lot of manual updates and unnecessary coding. They are also much easier to maintain and debug than module code. When confronted with the limitations of what you can do with SQL Syntax, you can supplement Access update queries with your own functions letting Access take care of updating the records, while you focus on the calculations.
Hope this helps!
Copyright © 2010-2014, FMS Inc. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc. The information provided in this document is provided "as is" without warranty of any kind.