HOW TO: Summary Page for Month-by-Month Report

[Wayfarer]

I've built a report using a aggregate query that is grouped by Month +
Year, Program and Name. When I feed the date ranges as query
parameters, it will very properly print out a report that shows a
separate page for each program by month with the names of the folks in
that program. So far, so good... But what I need to complete the
report is a summary page for the entire date range in the same format.
I have no &%^# idea how to do that.

Do I have to fire off a separate report using the same parameters from
an event in the month-by-month report, or is there an easier way? Can
anyone help me (with this problem...the rest of me is beyond help)?

If anyone's feeling particularly masochistic, the SQL statement is
included below.

TIA

Neill Dumont

==
SELECT tblCharges.month_yr, tblCharges.program, [last]+", "+[first] AS
Name, Sum(IIf([professional],[hours],0)) AS Prof,
Sum(IIf([unduplicated],1,0)) AS [1st Time],
Sum(IIf([gender]="Male",[hours],0)) AS Male,
Sum(IIf([gender]="Female",[hours],0)) AS Female, Sum(IIf(age([dob])
Between 6 And 17,[hours],0)) AS [6-17], Sum(IIf(age([dob]) Between 18
And 29,[hours],0)) AS [18-29], Sum(IIf(age([dob]) Between 30 And
65,[hours],0)) AS [30-65], Sum(IIf(age([dob])>65,[hours],0)) AS [65+],
Sum(IIf([ethnicity]="White",[hours],0)) AS White,
Sum(IIf([ethnicity]="Black",[hours],0)) AS Black,
Sum(IIf([ethnicity]="Hispanic",[hours],0)) AS Hispanic,
Sum(IIf([ethnicity]="Asian",[hours],0)) AS Asian,
Sum(IIf([ethnicity]="Native American",[hours],0)) AS NatAm,
Sum(IIf([ethnicity]="Other",[hours],0)) AS Other,
Sum(IIf([hours]>0,[hours],0)) AS TotHours
FROM tblVolunteers RIGHT JOIN tblCharges ON tblVolunteers.vol_id =
tblCharges.vol_id
WHERE (((tblCharges.month_yr) Between
[Forms]![frmSetReportRange]![txtStart] And
[Forms]![frmSetReportRange]![txtEnd]))
GROUP BY tblCharges.month_yr, tblCharges.program, [last]+", "+[first];

[Allen Browne]

Hi Neill. Several possiblities.

A simple and flexible one is to display the summary in a subreport in the
Report Footer section of your main report. The query for the subreport will
contain the same WHERE clause so that it covers the same records as the main
report, i.e.:
WHERE (((tblCharges.month_yr) Between
[Forms]![frmSetReportRange]![txtStart] And
[Forms]![frmSetReportRange]![txtEnd]))

It may be possible to add Running Sum controls to your report to accumulate
the values that you need for the Report Footer section. That works reliably.

Using code in the events of the report is NOT recommended. In many cases,
the code does not fire for pages that are not previewed/printed, so this
approach gives wrong results.

In cases where you cannot generate a simple query for the subreport, you
could create the SQL statement in code before you OpenReport, and assign it
to the SQL statement of the QueryDef that feeds the subreport. Best reserved
for when you are really stuck. (If you are utterly stuck, there's always a
temp table.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wayfarer" <ne**********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I've built a report using a aggregate query that is grouped by Month +
Year, Program and Name. When I feed the date ranges as query
parameters, it will very properly print out a report that shows a
separate page for each program by month with the names of the folks in
that program. So far, so good... But what I need to complete the
report is a summary page for the entire date range in the same format.
I have no &%^# idea how to do that.

Do I have to fire off a separate report using the same parameters from
an event in the month-by-month report, or is there an easier way? Can
anyone help me (with this problem...the rest of me is beyond help)?

If anyone's feeling particularly masochistic, the SQL statement is
included below.

TIA

Neill Dumont

==
SELECT tblCharges.month_yr, tblCharges.program, [last]+", "+[first] AS
Name, Sum(IIf([professional],[hours],0)) AS Prof,
Sum(IIf([unduplicated],1,0)) AS [1st Time],
Sum(IIf([gender]="Male",[hours],0)) AS Male,
Sum(IIf([gender]="Female",[hours],0)) AS Female, Sum(IIf(age([dob])
Between 6 And 17,[hours],0)) AS [6-17], Sum(IIf(age([dob]) Between 18
And 29,[hours],0)) AS [18-29], Sum(IIf(age([dob]) Between 30 And
65,[hours],0)) AS [30-65], Sum(IIf(age([dob])>65,[hours],0)) AS [65+],
Sum(IIf([ethnicity]="White",[hours],0)) AS White,
Sum(IIf([ethnicity]="Black",[hours],0)) AS Black,
Sum(IIf([ethnicity]="Hispanic",[hours],0)) AS Hispanic,
Sum(IIf([ethnicity]="Asian",[hours],0)) AS Asian,
Sum(IIf([ethnicity]="Native American",[hours],0)) AS NatAm,
Sum(IIf([ethnicity]="Other",[hours],0)) AS Other,
Sum(IIf([hours]>0,[hours],0)) AS TotHours
FROM tblVolunteers RIGHT JOIN tblCharges ON tblVolunteers.vol_id =
tblCharges.vol_id
WHERE (((tblCharges.month_yr) Between
[Forms]![frmSetReportRange]![txtStart] And
[Forms]![frmSetReportRange]![txtEnd]))
GROUP BY tblCharges.month_yr, tblCharges.program, [last]+", "+[first];