my query does not update table

[robnsd]

I have a query based on a table. This query also has a calculation in
it. When I run the query I want to be able to change the value for
some of the fields and have those changes reflected in the table.
However Access will not let me type anything into the fileds. Is it
because there is a calculation in the query? How do I get around this?

 

[John Spencer]

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
In the results window select About Updating Data and select the appropriate
option.

If you cannot edit the data in a query, this list may help you identify why it
is not updatable:
--Query based on three or more tables in which there is a many-to-one-to-many
relationship
--Query that includes a linked ODBC table with no unique index or a Paradox
table without a primary key
--Query that includes more than one table or query and the tables or queries
aren't joined.

--It has a TRANSFORM clause. A Crosstab query is always read-only.
--It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries
that aggregate records are read-only.
--It has a GROUP BY clause. A Totals query is always read-only.
--It contains a DISTINCT predicate (i.e.; Unique Values is YES in the query's
properties.)
--It involves a UNION. Union queries are always read-only.

--It has a subquery in the SELECT clause.
--It uses JOINs of different directions on multiple tables in the FROM clause.
--The fields in a JOIN are not indexed correctly: there is no primary key or
unique index on the JOINed fields.
--The query's Recordset Type property is Snapshot. Set Recordset Type to
"Dynaset" in the query's Properties.
--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.)

-- Access 2007: The query calls a VBA function, but the database is not in a
trusted location so the code cannot run.

Also see
http://allenbrowne.com/ser-61.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have a query based on a table. This query also has a calculation in
it. When I run the query I want to be able to change the value for
some of the fields and have those changes reflected in the table.
However Access will not let me type anything into the fileds. Is it
because there is a calculation in the query? How do I get around this?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If other fields (bound table fields) in the query aren't updateable, see John
Spencer's references, and/or copy and paste the SQL view of the query to a
message here, and indicate which fields you want to update and what's
happening when you try. Someone may be able to help.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com