Aggregating values month by month over a year

[jakobal]

Hello everyone,

I only have some basic knowledge in Access so I guess this is a simple problem, but any help will be much appritiated!

I have a list with records that include two columns, dates and numeric values. What I would like to do is to from the 1 st of July each year start to aggregate the values month by month. In example the table looks like:

Date Value
2010-07-05 5
2010-07-23 8
2011-08-06 4
2011-08-12 8
2011-09-26 3

And I would like to transform it to:

Month Value
Jul 13
Aug 25
Sep 28

And so on..

What I want in the end is to display a graph which shows the progress over the year. Can this be done in Access?

Thanks again for any help!

Jakob

 

[Taruz]

Hi..

This query gives the monthly totals.:

select 
   format(date_field,"mmm"), 
   sum(value_field) 
from table_name
group by format([date],"mmm")

 

[jakobal]

Hi Taruz,

Thanks for you answer.

I still tough have the problem of not aggregating the months together month after month. With the code you wrote, if going back to the example I wrote before, I get:

Jul 13
Aug 12
Sep 3

Instead of
Jul 13
Aug 25
Sep 28

Any idea of how to do this?

Thanks!

 

[Taruz]

I think I understand now..

select 
      format([date_field],"mmm") as tmmm, (
              select sum(value_field) from (
                             select date_field, value_field, month([date_field]) as tmonth
                                    from table_name as trz
                              order by month([date_field])
                                            ) as tt 
              where trza.tmonth>=tt.tmonth) as runSum
from (
            select date_field, value_field, month([date_field]) as tmonth
              from table_name as trz
order by month([date_field])
) as trzA

 

[jakobal]

Thanks again Taruz,

I'm not really making it work. I get the message:
"The Microssoft Office Access database engine cannot find the input table or query 'aatrz'. Make sure it exists and that its name is spelled correctly"

Do you know what I am doing wrong?

Thanks!

Jakob

 

[Taruz]

Hi Jakob..

My fault ..

I updated my message above .. Please try again..

 

[jakobal]

Hi Taruz and thanks again,

I just tried out the code and now it is returning values. I am getting one column with the month and one with the what I think is the aggregated value for the month and the previous months. It however seems to be one record for each record in the previous table. Going back to my first example, I am now getting a table that looks like:

Jul 13
Jul 13
Aug 25
Aug 25
Sep 28

Another problem is that it does not start from July, instead it starts from January. Maybe this can be solved by including the year? The source table I use has values from Jul (Year X) to Jun (Year X+1) (I limit the table by choosing a year in a combobox in a form)

Any clues?

Thanks!