Running Sum in Detail?

[kwooten]

I have a monthly management report which includes the names of the employee, # in the group they are responsible for and a MTD total of contacts they have made to their group. I want to include a YTD total in this report but the running sum function doesnt work and I have tried a DSUM but that gives me the same running total on each employee line for the entire group. I want a running total of contacts for each employee, so if in January the employee had 100 contacts and in February had 50 then my total should be 150 for that employee.. Any ideas?

My DSUM was: dsum("[Portfolio Contacts]","Relationship Mgt","datepart('m',[Date])<=" & [Month Number] & "")

I have a group on Month Number (since Access doesnt understand April 2012 is not first), and Employee Dept then a sort on Employee Last Name.. The running sum for the month works fine in the Month Number Footer where i have the total group summary.

[alansidman]

Look at this thread.

http://www.mrexcel.com/forum/microso...l-records.html

[June7]

What is the grouping heirarchy?

Show the DSum expression.

Want to provide db for analysis? Follow instructions at bottom of my post

[kwooten]

Group Heirarchy is Month Number, then Employee Dept. I have no group on the actual Employee.

=DSum("[Portfolio Contacts]","Relationship Mgt","[Month Number]<=" & [Month Number] & "")

db is too large to upload..

[June7]

Did you make copy, remove records (leave a few for testing), run compact & repair, zip?

[kwooten]

I only uploaded the qry i was using to try and get the running sum by employee for each month.

[June7]

Need to also include the employee in the DSum criteria.

ContactRunSum: DSum("[Contacts]","Contacttbl","[HRP Lastname]='" & [HRP Lastname] & "' AND [Created Month]<=" & [Created Month] & "")

And if you have multiple years, will also have to include criteria for the year.

Domain aggregate functions don't care about the filter or grouping criteria of the query they are in.

If you use domain aggregate function in a textbox on a filtered form or report, have to include the same filter criteria in the function if you want the data to have the same restrictions.

[kwooten]

Thanks.. i thought that was the case; i just couldn't figure out how to incorporate employee in the DSum. I wont have multiple years.

Instead of putting this DSum in the query, would it be more efficient to put it in the report? Not sure i understand "If you use domain aggregate function in a textbox on a filtered form or report, have to include the same filter criteria in the function if you want the data to have the same restrictions." DSum is a relatively new function to me.

[June7]

DSum is one member of a family of domain aggregate functions - DAvg, DMax, DLookup etc.

As example, if you want only the records for July and that filter is in the the query/report and you want the DSum to also be limited to July but the DSum datasource is the original table, then DSum must also have the filter criteria.