423,498 Members | 1,177 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,498 IT Pros & Developers. It's quick & easy.

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

P: 88
In a table:
Expand|Select|Wrap|Line Numbers
  1. SID   Date         Status
  2. 1     25/8/2017     P
  3. 1     26/8/2017     P
  4. 1     27/8/2017     A
  5. 2     25/8/2017     A
  6. 2     26/8/2017     P
  7. 2     27/8/2017     A
  8. 1     01/9/2017     P
  9. 1     02/9/2017     A
  10. 1     03/9/2017     A
  11. 2     01/9/2017     P
  12. 2     02/9/2017     A
  13. 2     03/9/2017     P
  14. ......................
  15. ......................
  16. 1     01/10/2017     P
  17. 1     02/10/2017     A
  18. 1     03/10/2017     A
  19. 2     01/10/2017     P
  20. 2     02/10/2017     A
  21. 2     03/10/2017     P
  22.  
Where SID and Date are (combined) set as Primary Key.

The result required is:
Expand|Select|Wrap|Line Numbers
  1. SID  Status  Month    CountThisMonth  TotalCountPrevious
  2. 1     P      8/2017        2                0
  3. 1     A      8/2017        1                0
  4. 2     P      8/2017        1                0
  5. 2     A      8/2017        2                0
  6. 1     P      9/2017        1                2
  7. 1     A      9/2017        2                1
  8. 2     P      9/2017        2                1
  9. 2     P      9/2017        1                2
  10. .............................................
  11. .............................................
  12. 1     P      10/2017       1                3
  13. 1     A      10/2017       2                3
  14. 2     P      10/2017       2                3
  15. 2     P      10/2017       1                3
  16.  
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....
Jan 8 '18 #1
Share this Question
Share on Google+
1 Reply


GazMathias
Expert 100+
P: 189
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.

Expand|Select|Wrap|Line Numbers
  1. "[your date field] < #" & Format([your date field],"mm/dd/yyyy") & '#'
  2.  
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
Jan 9 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.