update query with inner join and sum

[hedgracer]

I am trying to do an update query with inner join and sum. The sql I am trying to use is as follows:

UPDATE BusDevComm INNER JOIN Daily_Grs_Comm_Converted ON BusDevComm.Office = Daily_Grs_Comm_Converted.Office SET BusDevComm.Grs_Comm = Sum([Daily_Grs_Comm_Converted].[Converted_Grs_Comm]);

I get the following error message:

You tried to execute a query which does not include the specified expression "Grs_Comm" as part of an aggregate function.

The two tables (BusDevComm and Daily_Grs_Comm_Converted) are linked SQL Server 2005 tables that are updateable. When I use the following sql in SQL Server 2005 it works in SQL Server 2005:

update busdevcomm
set Grs_comm =
(select sum(converted_grs_comm)
from daily_grs_comm_converted
where daily_grs_comm_converted.office =
busdevcomm.office)

When I cut and paste the above sql into a query in Access 2003 it throws the following error:

Operation must use an updateable query.

I want to be able to just set up a query in Access 2003 that the user can run to perform this update. Can anyone give me advice on this. Thanks for all help in advance.

Dave

 

[PHV]

And what about this ?

UPDATE BusDevComm
SET Grs_Comm = DSum("Converted_Grs_Comm", "Daily_Grs_Comm_Converted", "Office='" & [Office] & "'")

If Office is defined as numeric then get rid of the single quotes.

Just a though: why not simply execute a stored procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886