DSum in Query

J

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?
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top