Update table from Result of Query

Hello!

I wanted to update a field of a table from the result of a
query. How should I do this? I tried putting the Select
statement of the query to the criteria of the update query
but I am getting the error 'Operation must use an
updateable query' Thanks.

Replace the "result of a query" with a domain aggregate function such as
DLookup() or DSum().

--
Duane Hookom
MS Access MVP


"JoeCL" wrote in message
...
Hello!

I wanted to update a field of a table from the result of a
query. How should I do this? I tried putting the Select
statement of the query to the criteria of the update query
but I am getting the error 'Operation must use an
updateable query' Thanks.

On Tue, 28 Sep 2004 18:17:28 -0700, "JoeCL"
wrote:

Hello!

I wanted to update a field of a table from the result of a
query. How should I do this? I tried putting the Select
statement of the query to the criteria of the update query
but I am getting the error 'Operation must use an
updateable query' Thanks.

Not all queries are updateable; any Query that has any totals
operation (Sum, Count, etc.) is not, for instance. Some table join
queries are updateable, some aren't.

Please open the query in SQL view and post it here. Someone should be
able to suggest a fix or a getaround.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

John,

Here's the SQL View.

UPDATE [Counter] SET [Counter].N01 = (SELECT Count(*)
FROM Survey1 WHERE 1 In (First,Second,Third,Fourth,Fifth));

The table Counter is the only table in the query window.

Thanks.

Joe

-----Original Message-----
On Tue, 28 Sep 2004 18:17:28 -0700, "JoeCL"
wrote:

Hello!

I wanted to update a field of a table from the result of
a
query. How should I do this? I tried putting the Select
statement of the query to the criteria of the update
query
but I am getting the error 'Operation must use an
updateable query' Thanks.

Not all queries are updateable; any Query that has any
totals
operation (Sum, Count, etc.) is not, for instance. Some
table join
queries are updateable, some aren't.

Please open the query in SQL view and post it here.
Someone should be
able to suggest a fix or a getaround.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.

On Tue, 28 Sep 2004 18:17:28 -0700, "JoeCL"
wrote:

Hello!

I wanted to update a field of a table from the result of a
query. How should I do this? I tried putting the Select
statement of the query to the criteria of the update query
but I am getting the error 'Operation must use an
updateable query' Thanks.

Joe, my ISP has for some reason locked your reply to my answer so I
can't download it. Please pardon the inconvenience and repost it for
me.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

On Tue, 28 Sep 2004 18:17:28 -0700, "JoeCL"
wrote:

Hello!

I wanted to update a field of a table from the result of a
query. How should I do this? I tried putting the Select
statement of the query to the criteria of the update query
but I am getting the error 'Operation must use an
updateable query' Thanks.

I had to go to Google Groups to find your reply:

John,

Here's the SQL View.

UPDATE [Counter] SET [Counter].N01 = (SELECT Count(*)
FROM Survey1 WHERE 1 In (First,Second,Third,Fourth,Fifth));

The table Counter is the only table in the query window.

Thanks.

Joe


The problem is that ANY query containing a Count operation is ipso
facto not updateable, even in cases (such as this) where it would be
logically reasonable.

The solution is to use the DCount() domain function to get the count -
IF you want to do this at all. I'm VERY queasy about a table design
with fields named First, Second, Third, Fourth and Fifth; and even
queasier about storing a derived-data value such as a count in a table
at all.

The short answer:

UPDATE [counter] SET [Counter].[No1] = DCount("*", "[Survey1]",
"[First] = 1 OR [Second] = 1 OR [Third] = 1 OR [Fourth] = 1 OR [Fifth]
= 1")

The better answer: Reconsider your table structure, and/or post a
description of that structure and explain what you're trying to
accomplish!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps