When can I update data from a query?

In some cases, you can edit data in query Datasheet view to change the data in the underlying table. In other cases, you can't. The following information shows whether a query's results can be updated, and if not, whether there is an alternative.

Data is updatable

You can update a query or query field in the following cases:

Data is updatable under certain conditions

If a query is based on tables with a one-to-many relationship, you might not be able to edit the data for the following query fields.

Query field Solution
Join field from the "one" side Enable cascading updates between the two tables.
New records, if the "many" side join field doesn't appear in the datasheet Add the join field from the "many" side to your query to allow adding new records.
Join field from the "many" side, after you've updated data on the "one" side Save the record; then you'll be able to make changes to the "many" side join field.
Blank field from the table on the "one" side of a one-to-many relationship where an outer join exists Enter values in fields from the table on the "many" side, but only if the joined field from the "one" side contains a value for that record.
New records, if entire unique key of ODBC table isn't output Select all primary key fields of ODBC tables to allow inserts into them.

Data can be deleted but not updated

Query or query field Solution
Query (or underlying table) for which Update Data permission isn't granted To modify data, permissions must be assigned.
Query (or underlying table) for which Delete Data permission isn't granted To delete data, permissions must be assigned. 

Data can't be updated

Query or query field Solution
Query based on three or more tables in which there is a many-to-one-to-many relationship Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
Crosstab query  None
SQL pass-through query  None
Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions. 
Union query  None
Query whose UniqueValues property is set to Yes  None
Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key  None
Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view You must join the tables properly in order to update them.
Calculated field  None
Field is read-only; the database was opened as read-only or is located on a read-only drive  None
Field in record that has been deleted or locked by another user A locked record should be updatable as soon as it is unlocked.