subtotalling question

[J Miller]

I have a report that is grouping and totalling by year/fund. When the
year changes I would like to show the total of the years but in the
same footer section as the fund:

year fund total
2001 001 500.00
2001 002 400.00
2001 003 100.00 year total 1,000.00

2002 001 100.00
2002 002 200.00
2002 003 500.00 year total 800.00


Instead of

year fund total
2001 001 500.00
2001 002 400.00
2001 003 100.00
year total 1,000.00

2002 001 100.00
2002 002 200.00
2002 003 500.00
year total 800.00

How can this be done?


Many thanks in advance.


Jeff

 

[Larry Linson]

It's easy to do if you use a separate Year Footer. That'll be in a separate
line, but would also be clearer that it is a total of all the funds -- to
me.

I can see how you could copy the fund detail/footer data into the year group
footer; but offhand, I don't see an good way to then set the preceding fund
detail/footer to invisible so CanShrink will eliminate it.

Larry Linson
Microsoft Access MVP

 

[Duane Hookom]

You can accomplish this by:
-add a text box in a year header
Name:txtGrpCount
Control Source: =Count(*)
-add a text box in the detail section
Name: txtSeq
Control Source: =1
Running Sum: Over Group
-add code to the On Format event of the detail section
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.MoveLayout = Me.txtGrpCount <> Me.txtSeq
End Sub