Report with Conditional Subtotals

[ND Pard]

If possible, how do I generate a report that displays a subtotal for a
position number ONLY if more than one person occupied the position during a
specific month:

Example:

Position Pay_Mo Employee_Name Salary

0003338 Jan John Doe $4,500

0003338 Feb John Doe $4,500

0003338 Mar John Doe $2,100
0003338 Mar New Guy $3,000
______
Subtotal $5,100

0003338 Apr New Guy $4,000

Etc.


Your help will truly be appreciated.

Thank you.

 

[John Spencer]

Group By Position
Add a new control to the detail section and set it as follows:
Name: tLineCount
Control Source: =1
Running Sum: Over Group
Visible: No

In the Group Footer's Format Event add the line
Private Sub GroupFooter0_Format (Cancel as Integer, FormatCount as Integer)
Cancel = Me.tLineCount = 1
End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Microsoft]

In the GroupFooter add a control called txtCount
In the control source enter =Count("*")

In the format section for the GroupFooter add the following

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)

If txtCount < 2 Then
Me.PrintSection = False
Me.MoveLayout = False
End If

End Sub

HTH
Al

 

[Marshall Barton]

If possible, how do I generate a report that displays a subtotal for a
position number ONLY if more than one person occupied the position during a
specific month:

Example:

Position Pay_Mo Employee_Name Salary

0003338 Jan John Doe $4,500

0003338 Feb John Doe $4,500

0003338 Mar John Doe $2,100
0003338 Mar New Guy $3,000
______
Subtotal $5,100

0003338 Apr New Guy $4,000

Add a text box (named txtTotal) to the group footer section.
Set its control source expression to =Count(*)

Then add a line of code to the group footer's Format event:

Me.footersectionname.Visible = (txtTotal > 1)

 

[ND Pard]

Thank you ALL very much.

I knew you'd come through for me.

 

[ND Pard]

To get a group to show a footer when there is more than one detail record in
that group (or Hide the footer when the group has less than 2 detail records)
I did the following:

I inserted a Text Box into the group footer section.

Note: if you right click on the group footer and display the "Property
Sheet", the Name of the Section if shown in the Property Sheet.
(In this example, the Name of the Section is: GroupFooter0.)

I named the Text Box: txt_Counter.
This text box's:
Control Source property was set to: =Count("*") (Refer to the Data tab.)
Running Sum property was set to: No. (Refert to the Data tab.)
Visible property was set to: No. (Refer to the Format tab.)

In the Format section of the group footer, I added the following code:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me.txt_Counter.Value < 2 Then
Me.PrintSection = False
Me.MoveLayout = False
End If
End Sub

The report performed as expected.
Thanks for your help guys.