Running Sum Issue

Hello experts,

I am trying have running sum based on category but I keep getting overall running sum. I attached a photo for reference. Tool number is text. ShiftEnd is date. Output is number. Please help.
Capture.PNG
Kai LeeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PatHartmanConnect With a Mentor Commented:
What is "category"?  It isn't in the query.  If you want to restrict the DSum() add additional criteria.

Running sums should almost never be calculated in queries.  The process in Access is very expensive.  Do it in your report.  Reports are liniar and so calculating a running sum presents no problem.  If you need the report to break on something, add sorting and grouping and choose "over group" rather than "over all" for the running sum.
1
 
Patrick MatthewsConnect With a Mentor Commented:
I assume that "by category" you mean "running sum by [Tool Number] and [ShiftEnd]".  If so, the short answer is that both conditions have to specified in your DSum criteria.

That said, PatHartman is 100% correct: using domain aggregates in a query like this is almost always a bad idea, and should be avoided.  Doing this in  a report is fairly trivial.  If you must have this do this in a query, I would do it as a self-join.
1
 
Kai LeeAuthor Commented:
Thank you all for answering my question. "By category" I mean running sum by [Tool Number] and [ShiftEnd]. I used report for running sum but it still showed overall running sum when I chose "over group".
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Kai LeeAuthor Commented:
Hello I attached another pic that shows what result what am I getting. Sorry I can't show the data. Capture.PNG
0
 
PatHartmanConnect With a Mentor Commented:
You have to group by Tool in the report.  Then the running sum will be for just that tool.

In your dsum, the criteria was >= tool.  It needs to be >= ShiftID and = tool
1
 
Kai LeeAuthor Commented:
Hello Pat,

After changing to Rtotal: DSum("[Output_Tools/Shift]","[Shifts]","[Tool Number]='" & [Tool Number] & "'" And "[ShiftID]>= " & [ShiftID]) see the result
Capture.PNG
0
 
Kai LeeAuthor Commented:
I will try to use report again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.