Best approach to report results by quarter

[John]

I am looking to build a performance measures database. Measures are input
monthly with narrative. Reports are generated quarterly and should show
summed results for each quarter as well as ALL the monthly narratives. The
ultimate report format should look something like:

Q1 Q2 Q3 Q4
YTD
t_measures numerator desc n n n n n
t_measures denominator desc n n n n n

All Monthly narratives (from t_results table) lie beneith the data.

where n is the sum of all monthly inputs (numerator and denominator) for
this particular measure as caputrued in the t_results table

I've tried to do sum queries and sum reports to no avail. Help is
appreciated.

 

[KARL DEWEY]

You can use a crosstab query with date formated on quarter.

 

[John]

This worked, one crosstab query for my numerator and one for my denominator.

Thanks

 

[KARL DEWEY]

You should be able to do it with one crosstab.
Post sample data and the SQL from your crosstabs.

 

[John]

Karl - Below is the SQL from the Numerator Crosstab. I am interested in
learning how to do this in one query but may not get to implement here as
I've moved on and addressed other learning opportunities (obstacles) based in
the dynamic field names etc. Not sure I have the wherewithal to step back
into that.

TRANSFORM Sum(t_Results.Numerator) AS SumOfNumerator
SELECT t_Results.MeasureID
FROM t_Results
WHERE (((t_Results.EndDate) Between DateAdd("q",-4,Date()) And Date()))
GROUP BY t_Results.MeasureID
ORDER BY "Qtr" & DateDiff("q",[EndDate],Date())
PIVOT "Qtr" & DateDiff("q",[EndDate],Date()) In
("Qtr0","Qtr1","Qtr2","Qtr3","Qtr4");