Only show subtotals for certain criteria

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have a contract report in my database, showing contract amount, change
orders, paid to date, remaining balance and percent complete. For some
contracts, the vendor works on more than one project ID. I have the report
set up to group by project ID and everything works lovely. However, I would
like to put subtotals in the project ID footer of the report. The catch is
that I only want the subtotals to show up when a particular project is
assigned to more than one project ID. Most projects are only for one project
ID so having the subtotal show for those is redundant becuase I already have
a contract total in the contract footer. Any suggestions? Please let me
know if my explanation was less than clear!

Thanks in advance :)
Stacey
 
Sure thing, Al!

This is what I see now:
ABC Seating PO#2322
Project ID: #1
Account Contract Amount Change Orders Paid to Date Remaining
Chairs $50 $(25) $25
$0
Tables $200 $0 $100
$100

Subtotal Prj #1 $250 $(25) $125
$100

Project ID: #2
Account Contract Amount Change Orders Paid to Date Remaining
Umbrellas $1,000 $250 $750
$500
Patio Tables $5,000 $0 $0
$5,000
Chairs $1,250 $0 $250
$1,000

Subtotal Prj #2 $7,250 $250 $1,000
$6,500

Total PO#2322 $7,500 $225 $1,125
$6,600

That part is perfect just how I want it. The next example is where I would
like to hide the subtotal by project line.

ABC Painting PO#2567
Project ID: #8
Account Contract Amount Change Orders Paid to Date Remaining
Paint $1,000 $250 $750
$500
Labor $5,000 $0 $0
$5,000

Subtotal Prj #8 $6,000 $250 $750
$5,500

Total PO#2567 $6,000 $250 $750
$5,500

For this example the subtotal row is unnecessary because it is only
repeating information from the PO total row. So for any contract with only
one project, I would like to hide the subtotal row.

Hopefully that makes sense!!

Thanks,
Stacey
 
Stacey,
Well that only happens when there is onlt 1 ProjectID associated
with a PO.
Usually in reporting, we let that little "anomaly" go... and there is
something to be said for "consistency" in displaying the sum of every
Project throughout the entire report.
But... I believe I've done what you're asking... when there were
always many more single entries in my report, than multiple.

Place an unbound text control in the PorjectID footer, and name it...
PIDCount
Control Source...
=Count(ProjectID)

Try this in the ProhectID footer's OnFormat event...

Private Sub YourFooterName_Format(Cancel As Integer, FormatCount As Integer)
If PIDCount = 1 Then
[SomeProjectIDSubTotalField].Visible = False
'... etc for all ProjectID subtotal fields
Else
[SomeProjectIDSubTotalField].Visible = True
'... etc for all ProjectID subtotal fields
End Sub

That may not be perfect, and I didn't have a chance to test, but that
should do the trick.
 
Back
Top