Search code examples
sqlms-accesssumsubquery

MS Access MTD to YTD values

I am trying to calculate out of MTD values YTD per month. Here is what ive got.

In MS access i have a table with titel, date and a value (which is MTD)

Titel    Date      Value
User 1   1.1.2020  10
User 1   1.2.2020  5
User 1   1.3.2020  20
User 2   1.1.2020  5
User 2   1.2.2020  15
User 2   1.3.2020  0

I need now a new column with the YTD values:

Titel    Date      Value    YTD Values
User 1   1.1.2020  10       10
User 1   1.2.2020  5        15
User 1   1.3.2020  20       35
User 2   1.1.2020  5        5
User 2   1.2.2020  15       20
User 2   1.3.2020  0        20

I realy did a lot of googling but its hard to find a solution for this in MS Access. And yes i need to use MS Access for that :)

Thank you for your help.

EI

Solution

  • In MS Access, you can use a subquery:

    select t.*,
       (select sum(t2.value)
        from t as t2
        where t2.titel = t.titel and
              year(t2.date) = year(t.date) and
              t2.date <= t.date
       ) as YTD
from t;