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).
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 ... |
Get Office 2007 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.