Incremental Count or sum

[epardo87]

Hello there,

I'm trying to recreate the following excel formula in a calculated field in an access query:

EXCEL: =SUMIFS(G:G,C:C,"<"&C2) where G:G is a "Score" column, "C:C" is the date column and "C2" its just the date in the current row, basically it is a SUM of all scores with earlier dates than the row's date.

In access I have columns [Score] and [GameDate] within table [Data], I've been trying to figure out and looking in the web, I have found DSum and DCount examples but not quite as I'm trying to use them and I get errors when trying to work some formula, I need the one summarising all scores regardless of the team and then another column would be summarising the team scored before that date, any thoughts?

the table has columns Key/GameDate/Team/Score

pd. also, would the solution work the same for counting instead of sum?

[June7]

Access doesn't handle this sort of query very well. Available methods can perform slowly with large dataset. Yes, same for count or sum or avg.

1. correlated subquery, review http://allenbrowne.com/subquery-01.html#YTD

2. domain aggregate functions

Provide sample data. Build table in post or attach file. Show your attempted code.