Edit data in a query

You may encounter situations where you cannot edit data in query Datasheet view to change the data in the underlying table. This article helps you understand when you can edit query data, when you cannot edit query data, and how to change the design of a query so that you can edit its underlying data.

Introduction

When you open a query in Datasheet view, you may find you want to edit the data. Perhaps you notice an error, or perhaps you see some information that is out of date. Depending on how the query was built, you can edit the data directly in the query datasheet.

If you try to edit the data in a query datasheet but nothing happens, or Windows plays a sound and your edit doesn't happen, you cannot perform the edit. It may be the case that the query itself is not available for editing, such as a crosstab query. It may also be the case that only the field you are trying to edit is not available for editing — for example, if the field is based on an aggregate function, such as an average. In either case, you may be able to do something to enable editing.

In addition to editing data in query Datasheet view, you can also use an update query to update data in a table. This article does not cover update queries.

For more information about update queries, see the article Create and run an update query.

Usually, when you want to edit data in query Datasheet view, you want your changes to be saved to the tables on which the query is based. If you do not want to change the data in those tables, but would still like to edit the data and keep the edited data after you are finished, you can use a make-table query to first create a new table whose data you can edit. You can also use a make-table query to save the results of a query that does not allow editing as a new table, and then edit the data in that new table. This article does not discuss how to create and run make-table queries.

For more information about make-table queries, see the article Create a make-table query.

Top of Page

When can I edit data in a query?

You can always edit the data in a query when the query is based on either only one table or on two tables that bear a one-to-one relationship to each other.

Note: Even when you can edit data in a query, some of its fields may not be available for editing. Such cases are listed in the following section.

Top of Page

When can I not edit data in a query?

You can never edit the data in a query when:

The query is a crosstab query.
The query is a SQL-specific query.
The field you are trying to edit is a calculated field. In this case, you may be able to edit the other fields.
The query is based on three or more tables and there is a many-to-one-to-many relationship.

Note: Although you cannot edit the query datasheet in this case, you can edit the data in a form when the RecordsetType property of the form is set to Dynaset (Inconsistent Updates).
The query contains a GROUP BY clause.

Top of Page

How do I change a query so that I can edit its data?

The following table lists cases when you cannot edit a query, paired with methods to make the query datasheet available for editing.

You cannot edit the values in a query datasheet when:	To make the query datasheet available for editing:
The Unique Values property of the query is set to Yes.	Set the Unique Values property of the query to No. See the following section, Set the Unique Values property to No, to learn how to set this property.
The query includes a linked ODBC database table with no unique index or a Paradox table without a primary key.	Add a primary key or a unique index to the linked table by using the methods provided by the vendor of the linked database.
You do not have Update Data permissions for the underlying table.	Assign Update Data permissions.
The query includes more than one table or one query, and the tables or the queries are not joined by a join line in Design view.	Create the appropriate joins. See the section, Create joins, to learn how to create the joins.
The database is open as read-only, or is located on a read-only drive.	Close the database and reopen it without selecting Open Read-Only; or, if the database is located on a read-only drive, remove the read-only attribute from the drive, or move the database to a drive that is not read-only.
The field in the record that you try to update is deleted or is locked by another user.	Wait for the record to become unlocked. A locked record can be updated as soon as the record is unlocked. Wait for the other user to finish the operation that has locked the record.
The query is based on tables with a one-to-many relationship, and the join field from the "many" side is not an output field. In this case, the data in the join field from the "one" side cannot be edited.	Add the join field from the "many" side of the relationship to the query output fields. See the section, Add the join field from the "many" side to the query output fields, to learn how to add the join field.
The join field from the "many" side (after you edit data) is on the "one" side.	Press SHIFT+F9 to commit your changes and refresh the query.
There is a blank field from the table on the "one" side of a one-to-many relationship, and the join is a right outer join.	Ensure that there is value in that field on the "one" side. You can edit the join field on the "many" side only if there is a value in that field on the "one" side.
You are using a linked ODBC database table, and not all of the fields from the linked table's unique index are in the query output.	Add all of the fields from the ODBC table's unique index to the query output fields. See the section, Add unique index fields from a linked ODBC table, to learn how to add the fields.

Top of Page

Set the Unique Values property to No

Open the query in Design view.
If the Property sheet is not open, open it by pressing F4. Click once in the query design grid to ensure that the property sheet displays query properties rather than field properties.
In the property sheet, find the Unique Values property box. Click the box next to it, click the arrow in that box, and then click No.

Create joins

Open the query in Design view.
For each table or query that you want to join to another, drag the join field from that table or query to the corresponding field in the table or query on which you want to create the join.

For more information about creating joins, see the article, Join tables and queries.

Add the join field from the "many" side to the query output fields

Open the query in Design view.
In the query designer, locate the join that corresponds to the relevant one-to-many relationship.
Double-click the join field from the "many" side of the one-to-many relationship. The join field appears in the field grid, indicating that it is now an output field.

Add unique index fields from a linked ODBC table

Open the query in Design view.
In the query designer, locate the linked ODBC table.
The unique index fields will have a key symbol next to the field name. Double-click each field that is not already in the field grid. Each field appears in the field grid, indicating that it is now an output field.

Top of Page

Microsoft Office Tutorials

Wednesday, December 13, 2017