Summing daily values by calendar month

[Adrian1962]

I have a series of daily date readings(over 3 years) and would like to sum
those by calendar month and year.

The table is:

Date Volume
1/1/07 100
1/2/07 200

I prefer to see twelve monthly values as follows...

Month Volume
Jan-07 300

 

[John W. Vinson]

I have a series of daily date readings(over 3 years) and would like to sum
those by calendar month and year.

The table is:

Date Volume
1/1/07 100
1/2/07 200

I prefer to see twelve monthly values as follows...

Month Volume
Jan-07 300

Create a Query based on your table. Include a calculated field SortMonth by
typing

SortMonth: DateSerial(Year([Date]), Month([Date]), 1)

in a vacant Field cell in the query grid. Select the Volume field as well.

Change the query to a Totals query by clicking the Greek Sigma button (looks
like a sideways M); Group By SortMonth and select Sum as the totals operator
for Volume.

Display the results in a form or report, and set the format property of the
textbox bound to SortMonth to mmm-yy.

John W. Vinson [MVP]

 

[Adrian1962]

I have a series of daily date readings(over 3 years) and would like to sum
those by calendar month and year.

The table is:

Date Volume
1/1/07 100
1/2/07 200

I prefer to see twelve monthly values as follows...

Month Volume
Jan-07 300

Create a Query based on your table. Include a calculated field SortMonth by
typing

SortMonth: DateSerial(Year([Date]), Month([Date]), 1)

in a vacant Field cell in the query grid. Select the Volume field as well.

Change the query to a Totals query by clicking the Greek Sigma button (looks
like a sideways M); Group By SortMonth and select Sum as the totals operator
for Volume.

Display the results in a form or report, and set the format property of the
textbox bound to SortMonth to mmm-yy.

John W. Vinson [MVP]

Hello John,

I pasted the following, "SortMonth:
DateSerial(Year([Date]),Month([Date]),1)" within the grid, and clicked the
subtotal. When I execute the query, a dialog box appears, "Enter a parameter
value", and the date I type in appears on every record under the calculated
field, "Sort Month". I then went to the Forms and created a form based on
query, and I see the daily sequential records versus a monthly summary. I
did edit the text box set the format property of the

textbox bound to SortMonth to mmm-yy, but still it does not roll up.

Sorry - just tough to decipher your solution.

 

[Adrian1962]

I have a series of daily date readings(over 3 years) and would like to sum
those by calendar month and year.

The table is:

Date Volume
1/1/07 100
1/2/07 200

I prefer to see twelve monthly values as follows...

Month Volume
Jan-07 300

Create a Query based on your table. Include a calculated field SortMonth by
typing

SortMonth: DateSerial(Year([Date]), Month([Date]), 1)

in a vacant Field cell in the query grid. Select the Volume field as well.

Change the query to a Totals query by clicking the Greek Sigma button (looks
like a sideways M); Group By SortMonth and select Sum as the totals operator
for Volume.

Display the results in a form or report, and set the format property of the
textbox bound to SortMonth to mmm-yy.

John W. Vinson [MVP]

Hello John,

I pasted the following, "SortMonth:
DateSerial(Year([Date]),Month([Date]),1)" within the grid, and clicked the
subtotal. When I execute the query, a dialog box appears, "Enter a parameter
value", and the date I type in appears on every record under the calculated
field, "Sort Month". I then went to the Forms and created a form based on
query, and I see the daily sequential records versus a monthly summary. I
did edit the text box set the format property of the

textbox bound to SortMonth to mmm-yy, but still it does not roll up.

Sorry - just tough to decipher your solution.

Hello John,

After some rest, I re-visited your solution, and typed in the correct field
name versus "Date", and it worked.

Thanks.

 

[mahmad]

Hi,

I want to do something similar, but what a comulatative sum.

Amount Date
------- -------
100 01/03/08
200 02/03/08

Total 300 for March so far then tomorrow it will add what ever value Amount
is.

Thanks
MO
and so on for the who month

On Wed, 26 Dec 2007 20:59:02 -0800, Adrian1962

I have a series of daily date readings(over 3 years) and would like to sum
those by calendar month and year.

The table is:

Date Volume
1/1/07 100
1/2/07 200

I prefer to see twelve monthly values as follows...

Month Volume
Jan-07 300

Create a Query based on your table. Include a calculated field SortMonth by
typing

SortMonth: DateSerial(Year([Date]), Month([Date]), 1)

in a vacant Field cell in the query grid. Select the Volume field as well.

Change the query to a Totals query by clicking the Greek Sigma button (looks
like a sideways M); Group By SortMonth and select Sum as the totals operator
for Volume.

Display the results in a form or report, and set the format property of the
textbox bound to SortMonth to mmm-yy.

John W. Vinson [MVP]

Hello John,

I pasted the following, "SortMonth:
DateSerial(Year([Date]),Month([Date]),1)" within the grid, and clicked the
subtotal. When I execute the query, a dialog box appears, "Enter a parameter
value", and the date I type in appears on every record under the calculated
field, "Sort Month". I then went to the Forms and created a form based on
query, and I see the daily sequential records versus a monthly summary. I
did edit the text box set the format property of the

textbox bound to SortMonth to mmm-yy, but still it does not roll up.

Sorry - just tough to decipher your solution.

Hello John,

After some rest, I re-visited your solution, and typed in the correct field
name versus "Date", and it worked.

Thanks.