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

  • Thread starter Thread starter Wayfarer
  • Start date Start date
W

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];
 
How do you expect to group/display the summary page? Is this totals by month
or by month and program? I would think the easiest method (if by month and
program) would be to create a totals query and use it as the record source
of a subreport:
SELECT tblCharges.month_yr, tblCharges.program,
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;


--
Duane Hookom
MS Access MVP
--

Wayfarer said:
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];
Click to expand...
 
Back
Top