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.