Sum Invoiced Amounts between dates in Control Source?

[eskybel]

Hello,

I have a table that holds all sales, by customer, including forecasted and invoiced amounts, in "periods," essentially the year, and the month in a single number, like 201301 means January 2013.
Ex:

CustNum	CustName	Period	Forecast	Invoiced
1	Bob	201301	100	95
1	Bob	201302	100	105
1	Bob	201303	100	110
1	Bob	201401	150	155
1	Bob	201402	150	145
1	Bob	201403	150	140

What I'd like to do is make a query/report that will make a quarterly review of each customer, comparing each year, showing growth/loss percent.

Ex:

CustNum	CustName	LY Qtr 1 Forecast	LY Qtr 1 Invoiced	CYQtr 1 Forecast	CY Qtr 1 Invoiced	Growth LY/CY Invoiced
1	Bob	300	310	450	440	30%

Easy way to do this? Essentially, I'd like to take a date range in the table, group them with something like =iif([Period]>=201301 and <=201303,sum([Invoiced])), in the Control Source on the report, or with a query I can tie the report to...if that makes any sense?

Thanks,
Adam

[June7]

Need a year and quarter identifiers then do a CROSSTAB query.

One way to calculate quarter from your data: Choose(Right([Period],2),1,1,1,2,2,2,3,3,3,4,4,4)

However, designing a report to run perpetually based on crosstab is not easy. CROSSTABs are so dynamic, especially with dates as column headers. Review http://allenbrowne.com/ser-67.html