When can I update data from a query?
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
In some cases, you can edit the data in query Datasheet view to change the data in the underlying table. In other cases, you can't. The following table shows whether a query's results can be updated, and if not, whether there is an alternative.
| Query or query field | Updatable | Solution |
|---|---|---|
| Query based on one table | Yes | |
| Query based on tables with a one-to-one relationship | Yes | |
| Query based on tables with a one-to-many relationship | Usually | For more information, click aa199585(v=office.10).md. |
| Query based on three or more tables in which there is a many-to-one-to-many relationship. | No | 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 | No | |
| SQL pass-through query | No | |
| 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 | No | 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. For more information, click aa219683(v=office.10).md. |
| Union query | No | |
| Query whose UniqueValues property is set to Yes | No | |
| Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key | No | |
| Query (or underlying table) for which Update Data permission isn't granted | Can delete but not update | To modify data, permissions must be assigned. For more information, click aa159350(v=office.10).md. |
| Query (or underlying table) for which Delete Data permission isn't granted | Can update but not delete | To delete data, permissions must be assigned. For more information, click aa159350(v=office.10).md. |
| Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view | No | You must join the tables properly in order to update them. For more information, click aa831328(v=office.10).md. |
| Calculated field | No | |
| Field is read-only; the database was opened as read-only or is located on a read-only drive | No | |
| Field in record that has been deleted or locked by another user | No | A locked record should be updatable as soon as it is unlocked. |
| Memo, Hyperlink, or OLE Object field in the query's results | Yes |