update query count

[Edrick larkin]

I am writing a database to keep track of program licenses. I have a service
table and a user/serv table. I want to update service.total_users so show
the number of installs of each program in the user/serv table (which simply
joins users with services). This is the following query I wrote, but it
gives me the error “Operation must use an updatable query.†Thanks for your
help. Edrick

UPDATE service SET service.total_users =
(SELECT distinct Count([user/serv].user_serv_key) FROM [user/serv]
WHERE service.service_key = [user/serv].user_serv_key);

 

[Chaim]

It is not generally a good idea to keep calculated fields in your tables.
Among other things, you- not the database- are then responsible to keep them
accurate. This means that everytime you take an action that changes the
calculated field or one of the component fields of the calculation, you have
to call extra code to keep the value accurate. It is much better to
calculate it on the fly.

From the Access Help:
This error occurs when the current query's Update To row includes a field
from either a crosstab query or select query in which an aggregate (total)
was calculated for the field (using either the Totals row or a domain
function in the Field row). To update a field using the aggregate of another
field, calculate the aggregate in the update query itself, not a different
query.

A count of users is easily determinable whenever needed. Using your UPDATE,
the total_users count that you want is simply your subselect. No need to
worry about update restrictions. If this is needed so that a control can
display this count, simply name that query and bind the control to this
query.

Good Luck!