Update table from Result of Query

  • Thread starter Thread starter JoeCL
  • Start date Start date
J

JoeCL

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().
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top