Report Footer not summing all records

[TammyT]

Simple report on file review results - the detail contains 8 sub-reports, 1 for each outcome measure; each sub-report contains the results info for the individual records by question & then a summary field for the total of all Yes & answers & all NA answers.

There is a footer by review number, to sum the Yes & NA answers for each sub-report (total of the sub-report SumYes fields, & SumNA fields).

There is then a footer by Agency, to get their overall results - that is pulling in the Yes & NA sums correctly for our 2 agencies.

Then there is the Report Footer - it is supposed to sum all Yes & all NA answers for the entire report; however, it's only pulling in the sums from the last agency displayed.

Example:

Agency 1 has 3456 Yes answers & 6080 NA answers

Agency 2 has 1242 Yes answers & 2185 NA answers

The Report Footer should have 4698 Yes answers & 8625 NA answers; however, it's only showing 1242 Yes & 2185 NA answers - same data as Agency 2.

I have tried moving fields/footers around every way I can think of, but it's not working.

Funny thing - I have another Db just like this one, same report, & it works perfectly with the same format/field info!

Help?

Thanks!

 

[randysmid]

Hi Tammy,
You may want to use DSum in your report footer to recalculate the values from your 8 sub-reports. I wrote an FAQ on how to use DSum. It's called "Use DSum function to retrieve totals" (faq703-3066).

HTH, Randy

 

[dhookom]

I would create one or more totals query that calculate the sums you want to display in your report. Then add these totals queries to your main report record source so you can easily sum the values without having to reference the subreports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

Thanks, all!

Actually - what I did was create 2 new reports, 1 for each agency - duplicated the queries & subreports as well - in the queries, set the Agency field = to each of the agencies.

Then I put each Agency report in the period ending header of the summary report - works great! I know this may sound like the long way around; however, I realized that I'm going to need agency-specific reports at some point anyway, so this really accomplishes 2 purposes anyway.

Again, thanks for the input - I'll save it for the future!