Access 3122 Error in Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
You make a mention of MS Access; so the first question to ask is to know if
this problem is with MS Access or with SQL-Server. This newsgroup is about
ADP and SQL-Server and - very important - the UPDATE syntaxe is different
between MS Access and SQL-Server; so it's hard to give you a correct answer.
 
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
 
Sorry, goofed the group name in my last post. Should have said:

microsoft.public.access.queries



Rob
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top