Update with Subquery in Access
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.
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.
Mark Edwards
Getting errors in the webpage code when clicking the Submit button. Way to go EE developers.... I'll try again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks.