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 methodology (see text) |
| Many-to-one-to-many | No | Can update data in a form or data access page if RecordType = Recordset |
| Two or more tables with no join line | No | Must have a join to determine updateability |
| Crosstab | No | Creates a snapshot of the data |
| Totals Query (Sum, Avg, and so on) | No | Works with grouped data creating a snapshot |
| Unique Value property is Yes | No | Shows unique records only in a snapshot |
| SQL-specific queries | No | Union and pass-through work with ODBC data |
| Calculated field | No | Will recalculate automatically |
| Read-only fields | No | If opened read-only or on read-only drive (CD-ROM) |
| Permissions denied | No | Insert, replace, or delete are not granted |
| ODBC tables with no primary key | No | A primary key (unique index) must exist |
| Paradox table with no primary key | No | A primary key file must exist |
| Locked by another user | No | Cannot be updated while a field is locked by another |
Overcoming query limitations
Table 36-1 shows that there are times when queries and fields in tables are not updateable. As a general rule, any query that performs aggregate operations or uses an ODBC (Open DataBase Connectivity) data source is not updateable. Most other queries can be updated. When your query has more than one table and some of the tables have a one-to-many relationship, there may be fields that are not updateable (depending on the design of the query).
Updating a unique index (primary key)
If a query uses two tables involved in a one-to-many relationship, the query must include the primary key from the one-side table. Access must have the primary key value so that they can find the related records in the two tables.
Replacing existing data in a query with a one-to-many relationship
Normally, all the fields in the many-side table (such as the tblSales table) are updateable in a one-to-many query. All the fields (except the primary key) in the one-side table (tblCustomers) can be updated. Normally, this is sufficient for most database application purposes. Also, the primary key field is rarely changed in the one-side table because it is the link to the records in the joined tables.
Design tips for updating fields in queries
If you want to add records to both tables of a one-to-many relationship, include the foreign key from the many-side table and show the field in the datasheet. After doing this, records can be added starting with either the one-side or many-side table. The one side’s primary key field is automatically copied to the many side’s join field.
If you want to add records to multiple tables in a form (covered in Chapter 37), remember to include all (or most) of the fields from both tables. Otherwise, you will not have a complete record of data in your form.