query with running totals

[fzxxx]

Hello. I have this table

Model year	MonthYear_text	CountOfID_W
XXX	2014 08	1
XXX	2014 08	9
XXX	2014 08	11
YYY	2016 01	2

I need to have a cumulative total by model as, for example

Model year	MonthYear_text	Expr1
XXX	2014 08	1
XXX	2014 08	10
XXX	2014 08	21
YYY	2016 01	2

But I am not able to set up the right formula. Not sure which kind of criteria I should add.



I am trying with

SELECT [table].[Model year], [table].MonthYear_text, DSum("[CountOfID_W]","[table]","[MonthYear_text]<= [MonthYear_text]" & " And [Model year]=" & '[Model year]' & "") AS Expr1
FROM table
ORDER BY [table].[Model year], [table].MonthYear_text;



Anybody could give me suggestions?
Thank you
F

[fzxxx]

Perhaps I have found. I need to add a auto-increment field to the starting table

then I can use:

SELECT [table].[Model year], [table].MonthYear_text, DSum("[CountOfID_W]","[table]","[ID]<=" & [ID] & " And [Model year]='" & [Model year] & "'") AS Expr1
FROM table
ORDER BY [table].[Model year], [table].MonthYear_text;

Is it the only way ?
Cannot I use [table].MonthYear_text instead of ID?

Thanks for all comments
F

[CJ_London]

try

SELECT [table].[Model year], [table].MonthYear_text, DSum("[CountOfID_W]","[table]","[MonthYear_text]<= '" & [MonthYear_text]" & "' And [Model year]='" & '[Model year] & "'") AS Expr1
FROM table
ORDER BY [table].[Model year], [table].MonthYear_text;

This assumes the Model Year is text type field and not a numeric field

[fzxxx]

try

SELECT [table].[Model year], [table].MonthYear_text, DSum("[CountOfID_W]","[table]","[MonthYear_text]<= '" & [MonthYear_text]" & "' And [Model year]='" & '[Model year] & "'") AS Expr1
FROM table
ORDER BY [table].[Model year], [table].MonthYear_text;

This assumes the Model Year is text type field and not a numeric field

Thanks for your reply

actually I get a syntax error in " & "'
in bold in the expression below:

SELECT [table].[Model year], [table].MonthYear_text, DSum("[CountOfID_W]","[table]","[MonthYear_text]<= '" & [MonthYear_text]" & "' And [Model year]='" & '[Model year] & "'") AS Expr1
FROM table
ORDER BY [table].[Model year], [table].MonthYear_text;

I try to understand how all these delimiters work... but in case you have already some ideas...
Thank you
F

[CJ_London]

my bad - remove the double quote here [MonthYear_text]" &