Update query syntax: SUM and GROUP BY

[BArnett]

Access 2002
-------
"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."
-------
Trying to run this update query on table tblContracts.

Field:
Select [EmployeeID], Sum([Fall09Contact]) as FCCou
FROM [tblCourseAssignments]
Group By [EmployeeID];
Table:
Sort:
Show:
Criteria:
or:

-------
Table tblCourseAssignments lists courses assigned to employees this year,
and the hours that they will earn for each course. Primary key is
CourseAssignmentID, foreign keys are EmployeeID, CourseID, and FiscalYearID.

Table tblContracts has new, empty fields where I intend to store total hours
the employees earn per semester for courses, based on the several rows per
employee in the tblCourseAssignments table. Primary key of tblContracts is
ContractID (AutoNumber), foreign keys are EmployeeID and FiscalYearID.

Referential integrity enforced between tblCourseAssignments and tblFaculty,
between tblContracts and tblFaculty, between tblCourseAssignments and
tblFiscalYears, between tblContracts and tblFiscalYears, and between
tblCourseAssignments and tblCourses.

 

[vanderghast]

With Jet, a query having a GROUP BY or an aggregate is marked not
updateable. Try using DSUM instead.

Field: field to be updated, NOT the expression updating it
UpdateTo: DSum("fall09Contact" ,"tblCourseAssignments" )



You can also add a third argument to DSum, to limit the records involved in
the summation.


Vanderghast, Access MVP

 

[BArnett]

Thank you! I appreciate your help.

Can you help me with the third argument in DSum? I want to have totals for
each employee. I tried the following, and although the syntax was valid, the
fields just updated to null.

DSum("Fall09Contact","tblCourseAssignments","EmployeeID"="tblCourseAssignments.EmployeeID")

 

[vanderghast]

Sure, give a try to:

DSum("Fall09Contact","tblCourseAssignments","EmployeeID=" & EmployeeID)



which assumes that EmployeeID is numerical (and not alpha-numerical). If it
is alpha-numerical, try, instead:

DSum("Fall09Contact","tblCourseAssignments","EmployeeID=""" &
EmployeeID & """" )


Vanderghast, Access MVP