Access SQL Question

[Kevin]

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,

 

[Allen Browne]

Perhaps a DCount() would work:

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

 

[bcap]

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]

Excellent suggestion. Works like a charm. Thanks much.