- it is NOT the number of tables in a query that makes it not updatable but it could be the relationships between the tables. For example, a query that uses a main table plus multiple lookup tables will be updatable (all things being equal). An example would be a Customer table that is linked to a CustomerType lookup table, a SalesRep lookup table, and a State lookup table will be updatable and that's 4 tables. Or if the tables have a hierarchical relationship so that they are 1-many-many as in Customer --> Orders --> OrderDetails, the query will be updatable.
There are a lot of things that can make a query not updatable regardless of how many tables are involved. Some are:
1. Aggregate functions such as Sum() or Max()
2. Undefined joins - if you look at a query in design view and do not see any join lines between the tables, the join is undefined and will produce a cartesian product which is not updatable.
3. Select Distinct - because it groups rows.
4. Union queries.
5. Lack of appropriate primary keys in the joined tables so that Jet cannot work out the cardinality of the relationship.
6. Using the 1-side key rather than the many-side foreign key in a query where you want to add rows to the many-side table.
7. A query that attempts to join unrelated tables such as Student, Parent, and Class. Both Parents and Classes are related to students. But they are not related to each other. So not only will this type of query not be updatable, the recordset returned will also be nonsensical.
This is only a few of the possible conditions that will cause the query to be not updatable.
