Access SQL Question

I'm trying to UPDATE the value of a COUNT() operation into a field viz.
UPDATE table1 SET field = ( SELECT COUNT(*) FROM table2
WHERE table1.PrimaryKey = 1;

When I do this I always get the error message
"Operation must use an updateable query".

Anybody know how to do this correctly?

thanks,

Perhaps a DCount() would work:

UPDATE table1 SET field1 = DCount("*", "table2")
WHERE table1.PK = 1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin" wrote in message
...
I'm trying to UPDATE the value of a COUNT() operation into a field viz.
UPDATE table1 SET field = ( SELECT COUNT(*) FROM table2
WHERE table1.PrimaryKey = 1;

When I do this I always get the error message
"Operation must use an updateable query".

Anybody know how to do this correctly?

thanks,

In general SQL terms you ARE doing it correctly. This insistence by Access
that you can't use non-updateable sub-queries in an UPDATE query is one of
it's most annoying "features".

You could probably get away with this (it doesn't smell so bad if you hold
your nose):

UPDATE table1 SET field = DCount("some_field", "table2") WHERE
table1.PrimaryKey = 1;

"Kevin" wrote in message
...
I'm trying to UPDATE the value of a COUNT() operation into a field viz.
UPDATE table1 SET field = ( SELECT COUNT(*) FROM table2
WHERE table1.PrimaryKey = 1;

When I do this I always get the error message
"Operation must use an updateable query".

Anybody know how to do this correctly?

thanks,

Excellent suggestion. Works like a charm. Thanks much.

"Allen Browne" wrote:

Perhaps a DCount() would work:

UPDATE table1 SET field1 = DCount("*", "table2")
WHERE table1.PK = 1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin" wrote in message
...
I'm trying to UPDATE the value of a COUNT() operation into a field viz.
UPDATE table1 SET field = ( SELECT COUNT(*) FROM table2
WHERE table1.PrimaryKey = 1;

When I do this I always get the error message
"Operation must use an updateable query".

Anybody know how to do this correctly?

thanks,