.....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 . |
| 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 . |
| 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 . |
| 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 . |
| 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 . |
| 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 |