Create Per Page Report Totals (Access 97)

Topic

New Reply

I need to create a calculated control on a report’s page footer. It needs to return the sum of a currency field (total) for all the records that show up on each page in the report. There are no grouping levels, just the page header, footer and detail section. Any suggestions on how to go about this? I don’t fear code so lay it on me.

Also, Is there any way to resolve this additional problem? – The page footer on this same report is not “attached” for lack of a better word, to the last record detail of the page. This means there is a space between the last record on the page and the page footer. Increasing the size of the detail section does not work and I cannot find a place in access where I can adjust the location of the page footer. This report is actually a replication of IRS forms that my company has to file so I need to get them as close to identical as possible.

Let me know if you need any further details in order to help. I welcome all feedback. Thanks. Jenn.

Reply | Quote

Replies

Second question first: put your “report footer” in a subreport instead of the report footer. You can set up a group footer that will print last before the report footer, and it should print right after the last of the detail.

The other question is addressed in the sample reports database available for download from Microsoft. Check this article for RptSmp97.exe, which is the 97 version.

Reply | Quote

The code from the Microsoft website did the trick but I would be lying if I said it actually made sense to me. If anyone would like to explain it.
Code is as follows:

Dim x as Double

report header_print
x=0

page footer_print
pagesum= runsum – x
x = runsum

***Where pagesum is the name of the unbound textbox in the page footer and runsum is an invisible textbox in the detail section who’s control source is the [Total] field.

I am interested in knowing the logic behind how this works as I would never have arrived at this solution myself.

Also, my report looks great, however the last page only has two records making more than half the page blank. Is there anyway to fill the remainder of the page with “empty” labels that have no data associated with it. (Note the way this report is set up is with a label containing the formating, in the background of the details section and the fields from the record source sitting on top of the label)

Lastly, I haven’t yet tried to implement the solution to the page footer articulation problem as I have not had the time. Will let you know how it turns out.

Thanks so much. Jenn.

Reply | Quote

Reports don’t have any way to display non-existent records, they only display what’s actually there. The detail section is printed once for each record in the recordset, so it won’t print for records that aren’t there.

You could probably fudge it with a subreport containing some kind of graphic representation of empty labels, but you wouldn’t be able to easily control the *number* of them you displayed. I’d suggest you forget about it.

As for your question about the code, the report header initializes the x variable to zero, then each page footer takes the current running sum of the Total field (I assume that runsum is set up as a running sum) and subtracts the previous running sum from the current value of x.

On page 1, x would = 0 and runsum would equal the sum of the Total fields on that page, so pagesum would equal runsum – 0. Then x is reset to the current running sum.

On page 2, x would equal the runsum from page 1, so pagesum would equal the total of both pages Total fields minus the page 1 sum of the Total fields, and so on.

Reply | Quote

You may not believe this but I thought your suggestion about using a graphic warranted further investigation. I counted the number of labels I was lacking on the last page (7) and copied/pasted 7 copies of just the formatted label into the “report footer” and lo and behold I have a complete form with 7 “empty” records. Also there are no gaps between the “real” records and the labels. It’s perfect.

Now, about the articulation problem. I have reviewed your solution and need some clarification. This page footer that does not articulate with the last detail on the page, is actually another formatted label and the pagesum control that holds the per page total. With that in mind, how can I put it in a subreport and still have it give me the per page total and where would this sub report go? There are no groupings either. It can’t go in the report footer as it needs to be present at the bottom of each page. Another possible avenue is the fact that there are exactly 9 detail records on each page – is there are creative solution that takes advantage of this?

Thanks again. Jenn.

Reply | Quote

I don’t have any trouble believing it. Just keep in mind that if the overall number of labels changes, you may have problems maintaining it.

I was talking about a report footer, not a page footer, being put into a subreport to snug it up to the last of the data on the final page. You can’t really change the location of the page footers, and they would look awful if you did. Having them in the same place on every page gives you a consistent presentation instead of never being sure exactly where the page footer is going to occur.

Are you saying that your labels don’t fill the same amount of space from page to page? Otherwise, how do you wind up with extra space between the labels and the footer on some pages and not others?

Reply | Quote

I apologize if my previous post was confusing (speaking of an articulation problem). But when you asked about the page footers being different, It got my wheels turning. I had noticed that on the first page only, there was less of a gap between the last detail and the page footer, however on all subsequent pages the gap was uniform. I realized that when I had entered the code for the pagesum that I had turned on the report header/footer. That had put additional space at the top of the first page of the report, thereby reducing the size of the gap. I then knew the solution was in the headers. I closed the space in the report header, increased the size of the page header, manipulated the header label and I’ll be darned…

I’ll thank you for all your help as I believe this horse is dead. Jenn.

Reply | Quote