Web Development

Ask something
Question Locked

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

0 helpful
2 replies

by maggioant 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

2 replies

by 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.

This discussion is closed to new replies.