Re: Access 3122 Error in Update query

There are a couple of problems with this, assuming that you're running this in an ADP project, and therefore using SQL Server as the
back end.

First, SQL Server UPDATE statements don't support aggregate functions (i.e. COUNT, SUM, etc.) in queries. You can get around that
using subqueries, though.

Second, your syntax would be wrong in any event.

Third, there's no GROUP BY clause in this statement, so I believe you would've gotten the total count of records in posts instead of
just the grouped count. (Not entirely sure, as I normally use COUNT(*), not COUNT(FieldName).)

In the end, this would be the syntax you're looking for:

UPDATE forums
SET posts = K.RecCount
FROM
forums AS F INNER JOIN
(SELECT forumid, COUNT(*) AS RecCount
FROM posts
GROUP BY forumid) AS K
ON F.id = K.forumid

Having said all that, as Sylvain mentioned, you seem to be wondering if *Access* doesn't support it, which makes me suspicious of
whether you're using Access ADP, or Access MDB; there's a substantial difference. If you're using an MDB, then the above syntax is
incorrect, I believe, and you should ask in a group such as microsoft.access.queries for help with that.


Rob

"Harun Resit GÜNES" <HarunReitGNE@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:693CDAEF-E060-4871-A108-62DFF84CF653@xxxxxxxxxxxxxxxx
Hi, I am trying to run this query,

UPDATE forums AS F INNER JOIN posts AS K ON F.[id] = K.[forumid] SET
F.[posts] = Count([K].[forumid])

And I am getting this error ;

You tried to execute a query that does not include the specified expression
'posts' as part of an aggregate function.

Why this error occurs? MS Access doesn't support aggregate Updates?
Please let somebody help me, thanks.


.

Quantcast