Calculate Year To date Totals

[Guest]

I have a table that contains the monthly values (one record for the monthly
totals of each account) from which I need to produce a report that shows not
only the value for each of the various financial fields for a particular
month, but an accumulated Year To Date (calendar year) value for each of the
various fields.

eg: The report for August would show the values for August plus additional
columns showing the sum of the monthly values January - August.

The month values aren't a problem but I'm struggling with how to calculate
the Year to Date totals.

Any suggestions and assistance would be much appreciated.

Thanks in advance,

Dave

 

[Allen Browne]

In report design view, open the Sorting And Grouping dialog.
Choose the date field, and in the lower pane of the dialog set:
Group Header: Yes
Group On: Year
This gives you a group header to put the year in.

On the 2nd row of the dialog, choose the date field again.
This time, set:
Group Footer: Yes
Group On: Month

In this Group Footer section, add another text box with these properties:
Control Source: =Sum([Amount])
Running Sum: Over Group
replacing "Amount" with the name of the field you want to sum YTD.

The text box will accumulate the totals over the group. Since the parent
group is the year, it will reset each year.

 

[Guest]

Allen,
Thanks for your quick reponse. I've had a look at what you suggested and it
goes some way to achieving what I need. Your suggestion seems to require each
successive month totals to be included on the report in order to achieve the
aggregated annual total, but what I really need is a summary report including
the totals for a single month (for each location), together with the full
year to date totals. Ideally they should be printed on a single row. See
below for a rough example of what I'm trying to achieve.

Month Location Interest(Month) Tax(Month) Interest(YTD)
Tax(YTD)
August
A 100.00 20.00
1000.00 200.00
B 50.00 10.00
500.00 100.0
-----------------------------------------------------------------------------------------------
Total 150.00 30.00
1500.00 300.00

------ end of report ------

I'm not sure of this can be achieved within the report itself or if it needs
to commence within the underlying query, as I need to limit the visible data
on the report to a single month (in the above case, August), per location,
but at the same time provide the year to date totals for the individual
locations as well.

Thanks & Regards,

David

In report design view, open the Sorting And Grouping dialog.
Choose the date field, and in the lower pane of the dialog set:
Group Header: Yes
Group On: Year
This gives you a group header to put the year in.

On the 2nd row of the dialog, choose the date field again.
This time, set:
Group Footer: Yes
Group On: Month

In this Group Footer section, add another text box with these properties:
Control Source: =Sum([Amount])
Running Sum: Over Group
replacing "Amount" with the name of the field you want to sum YTD.

The text box will accumulate the totals over the group. Since the parent
group is the year, it will reset each year.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table that contains the monthly values (one record for the monthly
totals of each account) from which I need to produce a report that shows
not
only the value for each of the various financial fields for a particular
month, but an accumulated Year To Date (calendar year) value for each of
the
various fields.

eg: The report for August would show the values for August plus additional
columns showing the sum of the monthly values January - August.

The month values aren't a problem but I'm struggling with how to calculate
the Year to Date totals.

 

[Allen Browne]

You are right: if you filter for one month, the approach I suggested will
not give the YTD figures.

Couple of alternatives:

1. A DSum() expression in the Control Source of the text box:
Example:

=DSum("Amount", "Table1", "([Location] = """ & [Location] &
""") AND ([InvoiceDate] Between " &
Format(DateSerial(Year([InvoiceDate]),1,1), "\#mm\/dd\/yyyy\#") & " And " &
Format(DateSerial(Year([InvoiceDate]), Month([InvoiceDate])+1,0),
"\#mm\/dd\/yyyy\#") & ")")

2. A subquery in the report's recordsource.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for your quick reponse. I've had a look at what you suggested and
it
goes some way to achieving what I need. Your suggestion seems to require
each
successive month totals to be included on the report in order to achieve
the
aggregated annual total, but what I really need is a summary report
including
the totals for a single month (for each location), together with the full
year to date totals. Ideally they should be printed on a single row. See
below for a rough example of what I'm trying to achieve.

Month Location Interest(Month) Tax(Month) Interest(YTD)
Tax(YTD)
August
A 100.00 20.00
1000.00 200.00
B 50.00 10.00
500.00 100.00
-----------------------------------------------------------------------------------------------
Total 150.00 30.00
1500.00 300.00

------ end of report ------

I'm not sure of this can be achieved within the report itself or if it
needs
to commence within the underlying query, as I need to limit the visible
data
on the report to a single month (in the above case, August), per location,
but at the same time provide the year to date totals for the individual
locations as well.

Thanks & Regards,

David

In report design view, open the Sorting And Grouping dialog.
Choose the date field, and in the lower pane of the dialog set:
Group Header: Yes
Group On: Year
This gives you a group header to put the year in.

On the 2nd row of the dialog, choose the date field again.
This time, set:
Group Footer: Yes
Group On: Month

In this Group Footer section, add another text box with these properties:
Control Source: =Sum([Amount])
Running Sum: Over Group
replacing "Amount" with the name of the field you want to sum YTD.

The text box will accumulate the totals over the group. Since the parent
group is the year, it will reset each year.

I have a table that contains the monthly values (one record for the
monthly
totals of each account) from which I need to produce a report that
shows
not
only the value for each of the various financial fields for a
particular
month, but an accumulated Year To Date (calendar year) value for each
of
the
various fields.

eg: The report for August would show the values for August plus
additional
columns showing the sum of the monthly values January - August.

The month values aren't a problem but I'm struggling with how to
calculate
the Year to Date totals.