Report help with Grouping

[Guest]

I am trying to generate a report of sales over a time period. The layout is:

YEAR
The 'Sorting and Grouping' is Year, Quarter, Month.

The problem I am having is with Running Sums. I have one field that does a
sum for the Month. I have another field that does the Running Sum over Group
for the Quarter. And lastly, I have a third field which should sum up the
sales for the Year. I tried to select Running Sum Over All, but that sums up
everything in the entire report including multiple years. Is there a way to
Sum over just 1 year at a time?

 

[Al Campagna]

James,
Try placing a calculated field in EACH Group Footer (Year, Quarter, Month) and in the
Report Footer (OverAll)
Set the calculated fields names to SumSalesByMonth, SumSalesByQuarter, SumSalesByYear,
SumSalesOverAll.
= Sum(SomeAmountValue) ' in each footer
SomeAmountValue must not be a calculated field itself... just a number.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Yep, that works. What if I wanted to have the Running Sum show up in the
Detail section? So that I can track it Year to Date (YTD)? Is that doable?

It would appear like this.

2007 - Q1 - January - $15, QTD - $15, YTD - $15
2007 - Q1 - February - $20, QTD - $35, YTD - $35
2007 - Q1 - March - $10, QTD - $45, YTD - $45
2007 - Q1 - April - $5, QTD - $5, YTD - $50
etc.

 

[Guest]

Anyone else have any ideas regarding the YTD problem?

Thanks!

 

[Guest]

The best method to get YTD totals might be to use a subquery in your report's
record source. Otherwise you might be able to figure out how to total
sections and create a running sum on the totals. Then add these values to
other running sums.