Trouble getting a running total

[keving]

I have a table called Team Standing that includes [TEAM] [PLAYER] [GAME DATE] [POINTS]. From this table I have a query that provides for each game date and team a count of the players, [GAME DATE] [TEAM] [PLAYER COUNT]. [PLAYER COUNT] is calculated using the total COUNT .

What I would like to add is a cumulative running total over the game dates. For example.

[GAME DATE] [TEAM] [PLAYER COUNT] [TOTAL PLAYER COUNT]

2007/01/05 --- 1 ------- 11 -------------- 11
2007/01/12 --- 1 ------- 8 -------------- 19
2007/01/19 --- 1 ------- 14 --------------- 33

I am having trouble figuring out how to get a cumulative running total. I have tried a number of different sub-queries and selects but I can’t seem to get something that works.

Any help or ideas would be greatly appreciated.

Thanks

 

[Peter Reid]

In your query that produces the 'Player Count' data, change the alias of the 'Team Standing' table to TeamStandingAlias

Then add the following expression to your query

RunningTotalPlayerCount: (SELECT Count(Player) FROM [Team Standing] Where [GAME DATE] <= TeamStandingAlias.[game date] AND Team=TeamStandingAlias.team)

 

[neileg]

Running sum is a standard function in a report. It may be the easiest approach.

 

[keving]

Thanks Peter that works perfectly. I was on the right track but was not aware of the need for the alias. I really appreciate your help.

Kevin …