id="info"

Question

Locked

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

By 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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

That's messy and slow and inefficient in SQL

by Tony Hopkinson In reply to How do I create a running ...

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.

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Software Forums