Calculating growth over a report grouping?

[sief]

Hi Everyone,

I have a subreport that is a listing of quarters with the sales and rebates for each quarter.
There is a total after each 4 quarters for the year totals.
I want to be able to calculate the year over year growth... but I can't remember enough about Access reporting to figure it out.

Here is a picture of what I would like the report to look like. I've managed to get everything working except the year over year growth.

 

[dhookom]

Typically, you could try an expression like the following to get the percent growth from 2008 to 2009:

=(Sum(Abs(Left([Quarter],4)="2009") * [Sales]) -  Sum(Abs(Left([Quarter],4)="2008") * [Sales]))/Sum(Abs(Left([Quarter],4)="2008") * [Sales])

You might be better of attempting to create this in a totals query and joining it to your report's record source. I don't care for hard-coding values like "2009" into expressions.

Duane
Hook'D on Access
MS Access MVP

 

[sief]

Thank you, that is a good start.

The problem I experience now is that I am grouping on year so the expression isn't able to see the data for the previous years. Is there a way to allow the report to see that other data?

Or is there a way to remove the year grouping and still have it go Q1, Q2, Q3, Q4, YearTotal, YOYGrown, Q1, ...?

 

[dhookom]

As stated "You might be better of attempting to create this in a totals query and joining it to your report's record source."

If we had information about your report's record source query and table/field names, datatypes, sample data, etc someone might be able to provide a solution.

Duane
Hook'D on Access
MS Access MVP

 

[sief]

The record source query has the following relevent fields:

Contract: the top grouping, creating a summary for multiple contracts
YearQuarter: The year and quarter
SALES1: Sales field
SALES2: Additional sales field
ALLSLS: Sum of Sales1 + 2
REBATES: Rebates field
YearOnly: Just the Year
QuarterOnly: Just the Quarter

There are some other fields I'm using for grouping at a higher level.

Currently I am grouping on Contract first, then on YearOnly. I have quarterOnly, sales1, sales2, and rebates in the detail section.
In the yearonly footer I have the sum of those fields.

The problem I have linking a total query is I don't know how to link it into the other data without either a) creating duplicate records or b) creating the same situation I have now where the report can't see data for any year other than the one it's currently writing the data for.

 

[dhookom]

You would probably need to create a totals query that groups by Contract and Year.

SELECT Contract, YearOnly+1 As PrevYear, Sum(Sales1) as PrevYrSales1, Sum(Sales2) as PrevYrSales2, Sum(Rebates) as PrevYrRebates
FROM Whatever
GROUP BY Contract, YearOnly+1;

You could then try to join this query into your main query on the Contract to Contract and YearOnly to PrevYear so you would have last years totals available in the Year Footer section. The join would need to show all records from the main query.


Duane
Hook'D on Access
MS Access MVP

 

[sief]

Thank you, Duane! That worked brilliantly!