Reports- Sum - sum of group

[markphsd]

Situation:
I have a report with 6 lines per page of a product. Sometimes the product has more then 6 lines so the report will carry over to the next page, the report could actually be several pages.

I need to get a sum of each page, at the bottom of each page. I don't want to get a running sum that accumulates as you page through it.

To reiterate:
page 1 could equal 5000
page 2 could equal 3000
page 3 could equal 6000

I have tried grouping, but I'm slow to understand when I can't find a example that suits me. I can't seem to get a total of a group for the lines, and even if i could, there is no way to group these lines. There isn't anything unique about them, other then they will be in record counts of 6. Can I group by Record count? Or is that not the way to go. Any help is appreciated

 

[Guest_imported]

I don't see any way to do what you are looking at. You are trying to get subtotals within a Group based on each page, and Access won't do that. You would think the PageFooter would be the place to sum values on that page, but I don't think that's going to happen. You can either sum at the Reports end, have a running sum, or sum in a Group Footer. Those are your only options.

Paul

 

[markphsd]

I've seen it done before. I guess I'll have to post the answer when I find it, or figure it out myself.

 

[WayneRyan]

Mark,

I think that you can do the following:

Header Print - Set unbound total(s) to 0
Detail Print - Add to unbound total(s)
Footer Format - Post unbound total(s) to footer

HTH,
Wayne

 

[markphsd]

Wayne,

I can set the unbound totals and I think I'll be able to post to footer, but how would I add to unbound total(s). Where'd you get the information, maybe I can look it up, or do you just know by experience.

Thanks

 

[PaulBricker]

Sounds like I was just plain wrong. I would be interested in seeing the solution if you do get it. I'm not clear what you mean by Set unbound total(s) to 0 or any of the other ones.
Thanks for any clarification.
Paul

 

[markphsd]

Paul,

I think this is what Wayne is getting at: you "refresh" the report for each new page. It starts off by setting the total to 0, then adds up all the line items and then display the total. Each time the page is changed, it will reset and re-add.

But still, i haven't figured it out. I'm not sure if I'm wrong, if he's wrong, or if you are wrong or if one of us is right. I hope Wayne that you are right.

Paul, this will be a good thing to know, if possible. I'll make sure you find out the answer if I do get one.

 

[WayneRyan]

Hi Mark,

I mean use the events that are associated with
the header, detail and footer sections of the
report.

I haven't done this for a while, but for A97 the
events are:

Page Header Format - Init all counters to 0
Page Header Print

Detail 1 format
Detail 1 print - Add to totals

.
.
.

Detail n format
Detail n print

Detail n+1 format ' Just to see that it doesn't fit
' on the page (don't trigger on format)!

Page Footer format ' Move to unbound controls if using vars
Page Footer Print

You can use declared variables on your code page
(just like on a form), or maintain all on unbound
controls on the report.

HTH,
Wayne

 

[WayneRyan]

Paul,

What I meant by unbound controls is to put some new
"fields" in the report footer. They are not fed by
the reports data source. They will hold the results
of the calculations mentioned above.

If you or Mark, while looking into this, find a way
to put something in the Header section, based on what
is found in the Detail section ... Please let me know!
To my knowledge the Header (A97) is not available
when the detail events fire.

Good luck,
Wayne

 

[PaulBricker]

I'm out of town of the next two days but here's what I did with little success.

Code in the ReportHeader Print event

Me.MyTotals = 0

Unbound textbox called MyTotals in the Detail Section with code in the Print Event that read

Me.MyTotals = Me.MyTotals + Me.TotalsHours

Unbound textbox in the PageFooter with code in the Format Event that read

Me.MySubTotals = Me.MyTotals

The first page set up properly. After that, nothing worked correctly. This is all in A2000 but I don't think that makes any difference. The Events fire in the same order as 97 as far as I know.
I'll catch up with this when I get back.
Thanks for the clarifications.

Paul

 

[markphsd]

markphsd (Programmer) Sep 26, 2002
Ok,

I made a mistake. I had mentioned that I saw what I wanted to do, but I'm sort of reverse engineering here. To reiterate I have to print a report that fits onto a insurance form for medicare. It had to have 6 lines per page and the total of those lines at the bottom. I didn't believe there was any way to group the lines because they didn't have a unique number that would correspond to 6 of the lines. I wanted to get away from adding a field just to group the lines.

However, there did happen to be a unique number that would group the lines in the format I wanted, it was used for a different insurance form.

By grouping and using a running sum I was able to get the total of the lines for each page. This is the result I wanted I was just trying to do it differently, since I didn't know how the original report was designed.

I tried all of the methods both of you mentioned, I don't think they work. If you set the total value to zero, then try to re add up the totals, the report won't add them, it stays at zero; that's what happened to me at least.

 

[PaulBricker]

Mark, this breaks down if you have any Null values in the field you are summing, but try this(change the FieldName where appropriate).

Put this in the PageHeader Format Event

Me.MyField = 0

Put this in the Detail Format Event

Me.MyField = Me.MyField + Me.MyValueField

Put this in the PageFooter Format Event

Me.MyPageTotalField = Me.MyField

It's currently working for me.

Paul

 

[markphsd]

Hey, Thanks paul.

I won't be using it for this report. But I'm sure i'll use it somewhere else.