How to get a cumulative sum or count in MS Access from a Table?

[mshakeelattari]

In a table:

SID   Date         Status
1     25/8/2017     P
1     26/8/2017     P
1     27/8/2017     A
2     25/8/2017     A
2     26/8/2017     P
2     27/8/2017     A
1     01/9/2017     P
1     02/9/2017     A
1     03/9/2017     A
2     01/9/2017     P
2     02/9/2017     A
2     03/9/2017     P
......................
......................
1     01/10/2017     P
1     02/10/2017     A
1     03/10/2017     A
2     01/10/2017     P
2     02/10/2017     A
2     03/10/2017     P
 

Where SID and Date are (combined) set as Primary Key.

The result required is:

SID  Status  Month    CountThisMonth  TotalCountPrevious
1     P      8/2017        2                0
1     A      8/2017        1                0
2     P      8/2017        1                0
2     A      8/2017        2                0
1     P      9/2017        1                2
1     A      9/2017        2                1
2     P      9/2017        2                1
2     P      9/2017        1                2
.............................................
.............................................
1     P      10/2017       1                3
1     A      10/2017       2                3
2     P      10/2017       2                3
2     P      10/2017       1                3
 

Similarly, in the next month, the TotalCountPrevious should show the total count of all the preveous months. I am able to get CountThisMonth but not understanding how to get the other one. I got a method for that one by googling, that is, DCount or DSum, and it worked for me. But, however, it is a very very slow process and took too time to run the query or the report.
So I request for help. Thanking in advance....

[GazMathias]

Hi mshakeelattari,

The solution is similar to what you have already implemented. You can use another DCount formula for the cumulative count but change its criteria such that it looks for dates prior to the row it is executing on and NOT equal to it like you are currently doing.

"[your date field] < #" & Format([your date field],"mm/dd/yyyy") & '#'
 

The problem I see is that the data you posted above does not appear to be storing dates as actual dates so I feel you may to have to jump through a few more hoops before you can solve this particular problem and scale the solution properly in the future.


Gaz