John W. Vinson said:
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.