If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
Access SQL Question
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, |
#3
|
|||
|
|||
Access SQL Question
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, |
#4
|
|||
|
|||
Access SQL Question
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, |
Thread Tools | |
Display Modes | |
|
|