SQL Query UPDATE & SELECT COUNT does not work

[pacman]

Hi
I want to count the number of cmdes from table CMDS and update field
CmdNb in another table CUST

UPDATE CUST SET CmdNb = (SELECT COUNT(*) FROM CMDS WHERE CUST.id =
CMDS.id);

The query does not work . I got an error msg # 3073 "Operation must
use an updateable query"

Any idea ?
Thanks for your help

 

[John Spencer]

You can use the DCOUNT function
UPDATE CUST
SET CmdNb = DCount("*","CMDS","CUST.id =" & CMDS.id)

If CMDS is a string instead of a number field then
UPDATE CUST
SET CmdNb = DCount("*","CMDS","CUST.id =""" & CMDS.id & """")

Another method would be to use a make table query based on CMDS table and then
use the table in the update query
UPDATE Cust INNER JOIN NewTable
On Cust.ID = NewTable.ID
SET Cust.CMDnb = [NewTable].[CountRecords]

Now after having said that, it is usually not a good idea to store this type
of value in your table. If a new record is added to CMDS you then have to
update the CUST table and if an existing record is deleted, you have to update
the CUST table. So you do run the risk of the number being out of synch with
reality.

Normally, you would just calculate the count in a query when you need it.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County