DSum in Query

[JudyB]

I am trying to create a calculated expression in a query to sum the total
weeks service each individual has worked in their current department. For
example an employee works 52 weeks in Sales, 52 weeks in Purchasing and then
returns to Sales where he is currently working and has been there 104 weeks.
I want the query to calculate the total time spent in the Sales department as
156 weeks. I have another query (Service Record Query) that shows each
individual along with their service time in each department they have worked.
I can get the total of all service time by using the following code:

Dept Time: DSum("[WeeksService]","Service Record Query","[EmployeeID]=" &
[Employees.EmployeeID])

I have tried adding & " And [DepartmentName] = DepartmentName") thinking
that it would sum only the weeks service in the individuals current
department.

Can anyone steer me in the right direction?

 

[Allen Browne]

Concatenate the value of the DepartmentName into the 3rd string.

Something like this:

Dept Time: DSum("[WeeksService]", "Service Record Query",
"([EmployeeID]=" & [Employees].[EmployeeID]) & ") AND ([DepartmentName] =
""" & Table2.DepartmentName & """)")

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Depending what you are doing, it could be much faster to use a Totals query.