Re: Access 3122 Error in Update query

Unfortunately, SQL is anything BUT universal. While implementations are very similar for the most part, there are moderate
differences in syntax amongst all the major databases...so don't just blame Access. :)

Since this is an MDB with a SQL back end, instead of an ADP, your best bet would probably be to post your question here:

microsoft.public.access.odbcclientsvr


Rob

"Harun Resit GÜNES" <HarunReitGNE@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:40147306-DD82-47F1-BF4C-12722D0B56E4@xxxxxxxxxxxxxxxx
Hello again, I am using Access MDB .
This query runs in SQL Server;

update forums set posts = (select count(forumid) from posts where
posts.forumid=forums.id)

But I need to run that query in MS Access,
I searched so long, but not found anything.
I am starting to dislike MS Access, if SQL is a Universal markup lang., then
any db engine in anywhere in world must support sql syntax and rules!!!

I tried to create a View in access, that it selects 2 columns as forum IDs
and count of posts from "posts" table. And tried to use it in access query
but didn't work.



"Robert Morley" wrote:

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.





.