updatable query

[rum23]

When I run this query in the query window in ACCESS, I can't add any records
to it...

"SELECT tblProjectBudgets.ProjectID, tblProjectBudgets.CIP_ID,
tblProjectBudgets.CIPBudgetItemID, tblProjectBudgets.ProjectBudget,
tblProjectBudgets.ProjectBudgetComments FROM (tblProjects INNER JOIN
tjunCIPProjects ON tblProjects.ProjectID = tjunCIPProjects.ProjectID) INNER
JOIN tblProjectBudgets ON (tjunCIPProjects.CIP_ID = tblProjectBudgets.CIP_ID)
WHERE (tblProjects.ROW_ID = 5)"

However, I have other similar SELECT queries such as one below and these
are updatable.

SELECT tblCIPBudgetAccounts.CIP_ID, tblCIPBudgetAccounts.CIPBudgetItemID,
tblCIPBudgetAccounts.AccountID, tblCIPBudgetAccounts.AccountCIPAmount,
tblCIPBudgetAccounts.CIPBudgetAccountComments, tlkpAccounts.AccountName
FROM tlkpAccounts INNER JOIN tblCIPBudgetAccounts ON tlkpAccounts.AccountID
= tblCIPBudgetAccounts.AccountID;

What's the difference? Is it something to do with multiple INNER JOINS?

 

[John Spencer]

Probably too many tables; but it could also be that you need to include the
primary key in the results from all three tables. Often the limit to have an
updatable query is two tables and in some cases even two tables is too many.

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Select that from the list for an explanation of some of the causes and
alternative solutions.

Access MVP Allen Browne has summarized the reasons:

Query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause (performs
aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM clause.
.. The query's Recordset Type property is set to Snapshot.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

http://allenbrowne.com/ser-61.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County