Running Count in Queries

[Guest]

Hi,

I am wanting to create a running count for the number of records in my
query. I have written a query with a date restrictor which should give me
weekly revenue for the last 12 weeks. Rather than counting to make sure I
have 12 I would like to create a field in my query that give the running
count of records. Can somone tell me how to do this?

Thanks,

 

[Michel Walsh]

Hi,


depends if you already have a GROUP BY clause or not and if you display one
record, or many, and if you have just one record by week, or not, by client.


Assuming you have one record per week per client:


SELECT a.ClientID, a.weekNumber, LAST(a.amount), AVG(b.amount), COUNT(*)
FROM myTable as a INNER JOIN myTable AS b
ON a.clientID=b.clientID
AND a.weekNumber >= b.weekNumber
AND a.weekNumber <= b.weekNumber + 12
GROUP BY a.ClientID, a.WeekNumber




where, in addition, AVG(b.Amount) returns the average of these (up to) 12
past weeks, for the given clientID



Hoping it may help,
Vanderghast, Access MVP