Multiple-column page totals

[SteveF]

Hello all,

I've been handed a situation that I've not encountered before.

Is there a way to design a report that's set up to have three columns of data across the page to report totals at the bottom of the page for each column?



Please see the attachment for an example of the requirement -- you'll note that the "column totals" section is repeated three times across the page. This is what I would need to imitate. Depending on the data involved, there could be up to two more identically-formatted pages that would follow this one, so I would want the "column totals" section to function as if it were a page footer.

TIA,
Steve

[June7]

Setting up the column format is easy, calculating an independent total for each column on each page is not. Can't find anything on the web about this.

This is a unique challenge but I have developed something. Review attached sample db. Open the report in PrintPreview.

Design has these requirements:

1. A unique ID field (or combination of fields) that the records will be ordered by. Autonumber datatype can serve this purpose.

2. A unique ID field that has no gaps in sequence. I have field in table just for this purpose and a function that will populate the field. I include it as an example just in case the unique ID in item 1 cannot be depended on for no gaps. I did not test with filtered recordset but think will work.

3. A query in which column and page numbers for each record are calculated and this query is used as RecordSource for the report.

4. DSum function in report page footer to calculate the column totals.

5. Structure report so the same number of rows will display on each page. This constant is necessary for the column number, page number, and totals calculations. This means no report header/footer on the same page as the first and last pages of detail records.

[SteveF]

June7,

Thanks for the example. I'll take a look at it to see how it works.

After the original post, it came to me that I could do what’s needed using a collection of sub-reports,each of which would just report the sub-range of rows needed for each column along with their totals, and a separate parent report for each “page”. Which page(s) would get printed is determined by the total row count in the data.

I'll evaluate both alternatives and see which works best. That PDF example isn’t just a “representative sample”; I actually need it to be formatted exactly the same as that Postal form: same number of rows per column,3 columns wide, etc. We’d be submitting the report to the Post Office as a ‘facsimile’ of the form I posted – while it may not have the USPS logo or use the same graphics or typeface, everything else on the original form needs to be duplicated in the report.

Steve

[June7]

The subreport approach did occur to me but couldn't see how to accomplish for multiple pages without a lot more VBA. I think will have to print out one 'page' at a time. Something I have to do in a complex multi-page report in my project. The example can be viewed at http://forums.aspfree.com/microsoft-...ry-322123.html

Regardless of the approach, the last page will probably never be full.

[SteveF]

June7,

After experimenting with both alternatives, I ended up going down the subreport path. I've attached a PDF of my version of the report using some fictitious data; you can see it gets pretty close in format to the original -- close enough that the USPS staff to whom we will submit the report has reviewed and accepted it.

VBA is not a problem for me; I'll be taking this now and wrapping some VBA-supported data-entry and reporting forms around it so that I can hand it off to other people in the company with no Access experience at all. As far as they'll be concerned, to print the report they just click the "Print" button; behind the scenes the number of pieces entered will be used to determine which of the 4 pages of the report get printed.

Thanks for your assistance,
Steve