Questions

How do I create a running "Count" in a MS Access Query?

+
0 Votes
Locked

How do I create a running "Count" in a MS Access Query?

maggioant
I need to create a running Count in a query. Ie. The query first needs to count records by month, and then a running count of all of them.

The query would look something like:

Yr Mth [CountOfPSI #] [TotalOfPSI #]
2009 7 5 5
2009 8 3 8
2009 9 2 10
  • +
    0 Votes
    Tony Hopkinson

    presonally I'd leave it for a UI or report, but if you really need to

    select Year,Month,CountOfPSI, SumCounts(Year,Month)
    From
    (
    Select Year,Month, Count(*) as CountOFPSI from SomeTable) dummytablealias


    Where SumCounts is a user defined function that
    does Select Count(*) From SomeTable Where Year <= @Year and Month <= @Month

    is one way to do it.

    Another is to use a cursor

    This might give you some ideas, don't go loony with them though, basic recomendation is avoid if possible.

  • +
    0 Votes
    Tony Hopkinson

    presonally I'd leave it for a UI or report, but if you really need to

    select Year,Month,CountOfPSI, SumCounts(Year,Month)
    From
    (
    Select Year,Month, Count(*) as CountOFPSI from SomeTable) dummytablealias


    Where SumCounts is a user defined function that
    does Select Count(*) From SomeTable Where Year <= @Year and Month <= @Month

    is one way to do it.

    Another is to use a cursor

    This might give you some ideas, don't go loony with them though, basic recomendation is avoid if possible.