I am trying to update a access table with data from this table. Specifically the account.
UPDATE PRODUCTO P, HISTORICO H SET V1= count(menaje)
WHERE P.codigo=H.codigo;
The thing is, it gives me an error that it does not include V1 as part of an aggregate function. I know that this is solved by putting the GROUP BY but I am unable to find where.
If I remove the count(menaje) and leave it as household works well.
You can try to save the result of COUNT in one variable and then make the UPDATE :
SET @COUNT_MENAJE=(SELECT COUNT(codigo) FROM HISTORICO H WHERE
P.codigo=H.codigo);
UPDATE PRODUCTO P SET V1=@COUNT_MENAJE;
Assuming that you want to update the V1 field in the PRODUCTO table with the count of the number of records that you have with your codigo in the HISTORICO table, you could simply do a% normal UPDATE equaling the field to be updated with the SELECT COUNT of the other table, such that:
UPDATE PRODUCTO P SET V1= (SELECT COUNT(codigo) FROM HISTORICO H WHERE P.codigo=H.codigo);
Another thing that you could try is to make a loop to go doing the UPDATES . Something like this:
dthistorico.Open "SELECT codigo, COUNT(menaje) AS contador FROM HISTORICO GROUP BY codigo", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do Until dthistorico.EOF
CurrentProject.Connection.Execute
"UPDATE PRODUCTOS SET V1=" & dthistorico.Fields("contador") & " WHERE codigo=" & dthistorico.Fields("codigo")
dthistorico.MoveNext
Loop
dthistorico.Close