Creating a Running Total for Each Record

[FinanceGuy]

With the four columns on the left down below, I want to be able to populate the fifth column (Year to Date Revenue):

Subscriber	Month	Year	Monthly Revenue	Year to Date Revenue
Apple	1	2020	$100	$100
Apple	2	2020	$300	$400
Apple	12	2020	$1,250	$1,250
Pepsi	1	2020	$200	$200
Pepsi	2	2020	$500	$700
Apple	3	2021	$100	$100
Apple	4	2021	$400	$500
Pepsi	1	2021	$600	$600
Pepsi	2	2021	$1000	$1,600

What is the best function or query to get the Year to Date Revenue column populated?

I've been experimenting with DSum, but cannot seem to get it to work. I am open to creating a temporary table to calculate and get the fifth column and joining that table and appending the data from that column into my master table.

 

[conception_native_0123]

does the query in this sample give you an idea of what you can do to get it done?

 

[plog]

DSum is the easiest way, however a correlated subquery (https://en.m.wikipedia.org/wiki/Correlated_subquery) is the most effecient.

Can you post your DSum attempt?

 

[Isaac]

Dear Mr Gates, when you are done settling your ... personal business ... and have time for Access again, please give us Windowing.

 

[CJ_London]

in a query Join the table to itself on subscriber, alias one as A and the other as B.

bring down the fields from A and the monthly revenue from B

it is unfortunate you have month and year as separate values since this makes the next bit more complicated as you have to combine then to create a sortable order.

The calculation you need is (yearx100) + month. It is also unfortunate that both month and year are reserved words and should not be used as field names so you will need to enclose these in square brackets,

So the criteria you need is where the A calculated value is >= to the B calculated value.

now convert your query to an aggregate query (group by)

group on the A fields and sum the B field

you can also sort on the A subscriber and calculated value to get the right order. If this is for a report you will need to bring the A.calculated value through because sorting is done in the report - query sorts will be ignored

 

[arnelgp]

ytd, for each year.

 

[conception_native_0123]

Dear Mr Gates, when you are done settling your ... personal business ... and have time for Access again, please give us Windowing.

good luck with that! don't forget to address his earthly-covered bunker that he sleeps in over there in washington state!

 

[FinanceGuy]

ytd, for each year.

Thanks Arnel. This works!

 

[access2010]

does the query in this sample give you an idea of what you can do to get it done?

Thank you for posting this Query, which helps me with one of my projects.
Mehran