[odi]

I have the following problem:
I must update a field in a table in an Access database. The value is saved in another table, where I have to count records grouped by an ID.
My solution is to run a query that does the counting and wrap this in an update statment:

PHP Code:

Dim SQLString As String



SQLString = "UPDATE tblClient "

SQLString = SQLString & "SET total = subclient.counter "

SQLString = SQLString & "FROM "

SQLString = SQLString & "( "

SQLString = SQLString & "      SELECT FK_Client, COUNT(FK_Client)  counter "

SQLString = SQLString & "      FROM tblSubClient GROUP BY FK_Client"

SQLString = SQLString & ") subclient "

SQLString = SQLString & "WHERE tblClient.ClientID = subclient.FK_Client"



DoCmd.RunSQL (SQLString) 

Something seems to be wrong, because I always get an error message saying "missing operator" (from MS Access).

Now I don't know if it's a general SQL syntax problem or is it because of the table alias (?)

Any idea?

[UnrealEd]

i think your query should be something like this:

UPDATE
  tblClient
SET
  total = (
    SELECT 
      COUNT(FK_Client) 
    FROM
      tblSubClient
    WHERE
      subclient.FK_Client = tblClient.ClientID
  ) 

[odi]

I figured out that this is not possible with MS Access, because it does not allow updates based on aggregate functions (such as count(),sum(),min(),max() etc.).

I did the most ugly thing that came up my mind:

I loop though my customer ids and update each records with a single statement. But there are only about 30 customers, so it shouldn't be that bad.

[ted_250]

Originally Posted by odi

I figured out that this is not possible with MS Access, because it does not allow updates based on aggregate functions (such as count(),sum(),min(),max() etc.).

I did the most ugly thing that came up my mind:

I loop though my customer ids and update each records with a single statement. But there are only about 30 customers, so it shouldn't be that bad.

I've seen similar updates that used aggregates; they used an unusual syntax similar to:

UPDATE table1 AS T1
INNER JOIN Table2 AS T2 ON T1.PK = T2.PK
SET T1.Col = T2.Col

Ted