Office 2007 Bible

Understanding Multi-Table Query Limitations

When you create a query with multiple tables, there are limits to which fields can be edited. Generally, you can change data in a query’s recordset, and your changes are saved in the underlying tables. The main exception is a table’s primary key—a primary key value cannot be edited if referential integrity is in effect and if the field is part of a relationship.

To update a table from a query, a value in a specific record in the query must represent a single record in the underlying table. This means that you cannot update fields in a query that transforms data because most transformations group records and fields display aggregate information. Each field in a transformed recordset represents multiple fields in the underlying tables. There is no way to change the data in a transformed field and have it reflected in the underlying tables.

Updating limitations

In Access, the records in your tables may not always be updateable. Table 36-1 shows when a field in a table is updateable. As Table 36-1 shows, queries based on one-to-many relationships are updateable in both tables (depending on how the query was designed).

Table 36-1. Rules for Updating Queries

Type of Query or Field	Updateable	Comments
One table	Yes
One-to-one relationship	Yes
Results contains Memo field	Yes	Memo field updateable
Results contain Hyperlink	Yes	Hyperlink updateable
Results contain an OLE object	Yes	OLE object updateable
One-to-many relationship	Mostly	Restrictions based on design ...