Running Sum In Query

[skilche1]

Hi all,

I am trying something very different and I need some help with coding. I am trying to do a running sum of defects over time per area discrepancy missed. I have searched and the net and this forum, but I can seem to find for results are running sum according to date.

I have attached an Excel version of what I am trying to accomplish. Note at the bottom of the graph, I have numbers, but in the graph I cam trying to reproduce in Access (version 2010), I want a weekly results (based on the beginning of the week). The Excel as you see is how the data is queried would appear with the DSUM being the Running sum (formulated in Excel for demonstration purposes).

Can anyone help me out here?

Thank in advance,
Steve

 

[CJ_London]

it can be done with a couple of provisos on the data you have provided.

The first is that the data is sorted by area catch matched and date caught.

The second is that you do not have two dates caughts the same for any area catch matched. If you do, you'll need a further column or columns to be able to determine which one 'comes first'

So based on the data provided, the solution is

 SELECT *, (SELECT SUM([TOTAL]) FROM myTable AS T WHERE [area catch matched]=myTable.[area catch matched] AND [date caught]<=myTable.[date caught]) AS RunningSum
 FROM myTable
 ORDER BY [area catch matched], [date caught]

 

[gemma-the-husky]

note this is doable, but is not trivial, and may be very slow for large data sets.

it is not trivial for the same reasons explained in this contemporary thread. see my comment #10

http://www.access-programmers.co.uk/forums/showthread.php?t=278254

 

[vbaInet]

Fields: "[Area Catch Missed]" and "[DateCaught]"

It will be slow but that's when you try to scroll to the last record. So to help improve performance you may follow these guidelines:
* index the fields mentioned above
* the source query used in the subquery proposed by CJ_London should only include the two fields mentioned above
* the subquery shouldn't be substituted for the DSum() function.
Also remember to sort the query used in the subquery by both fields before calling it in the subquery.

Other quicker alternatives are:
* Do it in a report
* Perform the calculation once and store the results in a table if and only if the data doesn't change regularly.

 

[CJ_London]

@Vbalnet - good points, I had based on data provided rather than the data source to the data - also fully agree with "[Area Catch Missed]" and "[Date Caught]" being indexed

A possible alternative assuming TOTAL is a count would be

 SELECT DISTINCT [area catch matched], [date caught], (SELECT Count(*) FROM myTable AS T WHERE [area catch matched]=myTable.[area catch matched] AND [date caught]=myTable.[date caught]) AS Total, (SELECT Count(*) FROM myTable AS T WHERE [area catch matched]=myTable.[area catch matched] AND [date caught]<=myTable.[date caught]) AS RunningSum
FROM myTable
ORDER BY [area catch matched], [date caught]