Update with Subquery in Access

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
Hi,
This is an Access Update query and subquery.
 There is a table called [zControl Entries]. It is supposed contains various other tables' total records. Therefore one entry in this table [zControl Entries] is called [Item Master], and this table [Item Master] is another table also, whose records are to be stored here in [zControl Entries].

Therefore I fire a query, and get an error 'Operation must use an updatable query'. Following is my query.

UPDATE [zControl Entries] As Ce
SET Ce.NumericValue = (Select Count(*) As NumericValue From [Item Master])
Where Ce.EntityType='Item Master'

Here 'Item Master' is a value in Ce table, whereas [Item Master] is also a physical table in the same database.

Thanks,
San.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark EdwardsChief Technology Officer

Commented:
Getting errors in the webpage code when clicking the Submit button.  Way to go EE developers....  I'll try again
Chief Technology Officer
Commented:
I tested your query and it doesn't like using the summation subquery to update the field value.  I changed the summation query to a select query using a numeric field in the Item Master table and it didn't like that either.  I think using a subquery as the source of the value to put in Ce.NumericValue is what Access doesn't like.

I changed the result of the subquery to a field in a table and it updated as expected.

To EE Developers:  Guess what, you can't submit any text with "Item Master" in brackets (I replaced it with "Item Master table" above).  It barfs your code.  I don't know how the author got away with it...
EE-Code-Does-Not-Like-Item-Master.PNG
Sanjay GandhiFounder, Kenhal

Author

Commented:
Well, this is what I also finally did. It did not give me result with single query. So I saved the result of COUNT(*) query using table [ItemMaster] in a variable, and later used UPDATE query to update that variable's value in another table [zControl Entries] where it is supposed to save.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial