• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 42
  • Last Modified:

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
0
Kai Lee
Asked:
Kai Lee
  • 4
  • 2
3 Solutions
 
PatHartmanCommented:
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 MatthewsCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
PatHartmanCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now