Updating Records in a Query

Suppose you create a query. You review the records and want to make some changes to the stored records in the underlying table(s). Will your editing be successful?

The dreaded answer is, "It depends." Let's try entering records in a query under different conditions to see what works and what doesn't.

I'm using the Categories and Merchandise tables for this example. In the Database window, choose Tools, Relationships to open the Relationships window. As you've seen previously, the two tables have a one-to-many relationship through the CategoryID field. The Categories table is on the "one" side, and the Merchandise table is on the "many" side. Close the Relationships window.

Update Records in One Table

Let's first review updating records in just one table.

1.	In the Database window, click the Tables button and select tblCategories. Choose Insert, Query. With Design View selected, click OK.
2.	Save the query as qryCategoriesAndProducts.
3.	In the Categories field list, add the CategoryName and CategoryDescription fields to the design grid.
4.	Click View to see your records.
5.	In the Jewelry record, click in the Description column. Add a comma after watch and type bracelet.
6.	Press Enter. Because the query is based on only one table, you had no problem adding the text.

Update Records in Two Tables that Have a One-to-Many Relationship

Things get more complicated when you want to add records to a query based on two tables that have a one-to-many relationship.

1.	Click View to return to Design view.
2.	Click Show Table. Select the tblMerchandise table and add it to the Design window.
3.	Add the MerchName and MerchDescription fields to the grid.
4.	Click View to return to Datasheet view.
5.	As an artificial exercise, click in the last row of the Category Name field. Type Jewelry and press Tab. Type Pendant, charm, watch, bracelet. Press Tab and try to type Bracelet. Access won't let you. Press Escape once (or twice, if necessary) to erase your entries.
6.	Click View to return to Design view. From the Merchandise field list, add the CategoryID field to the design grid. Drag the CategoryID column so that it's the first column in the grid. Your Query Design window should look similar to Figure 8.17. (I made the panes equal in size and lengthened the field lists for a better view.) Figure 8.17. A multitable query in Design view. Be sure that the CategoryID in the first column is from the Merchandise table.
7.	Click View to return to Datasheet view. In the CategoryID field, type 5, which is the ID for jewelry, and press Tab. Access automatically enters the values for the Category Name and Description fields.
8.	Press Tab twice. Type Bracelet in the Product Name field. Access now has no problem accepting an entry in the Product Name field.
9.	Press Undo Current Field/Record to erase the entry. Note that you could have made an additional entry in the Item Description field and saved this record like any other.
10.	Close the query and click Yes to save your design changes.

What just went on here? Access initially didn't allow you to enter new records in the multitable query. Then you added the join field from the "many" side of the relationship (namely, CategoryID from the Merchandise table) to the query. When that field was part of the query, Access accepted new records.

Although these two examples are useful, they do not tell the whole story. If you intend to use queries significantly for updating data, you'll want to take a look at the article "When Can I Update Data from a Query?" in the "About Updating Data" section of Access Help.