I have a set of projects that have an estimated cost (budget). These
projects are assigned to a set of contractors, so that each contractor
has multiple projects. As work is completed, the contractor invoices
against a specific contract.

The report I'd like to write has the ability to report if the
contractor has invoiced over the total budget for all of their
projects.

The source recordset would have rows similar to the following:

contractor = 1
project = 1
invoice = 1
invoiceAmount = 100
estimatedCost = 5000

contractor = 1
project = 1
invoice = 2
invoiceAmount = 200
estimatedCost = 5000

contractor = 1
project = 2
invoice = 1
invoiceAmount = 2100
estimatedCost = 15000

contractor = 1
project = 2
invoice = 2
invoiceAmount = 3200
estimatedCost = 15000

contractor = 1
project = 2
invoice = 3
invoiceAmount = 177.53
estimatedCost = 15000

etc.

Basically, the estimated cost is repeated in every row of the
recordset.

So "=sum(InvoiceAmount)" gives me the correct amount that the
contractor has invoiced.

But "=sum(estimatedCost)" gives me a bogus amount based on the number
of invoices.

What I'd like to do is in the group footer for the contractor is to
compare the total invoiced for all the projects for the contactor
against the total estimated costs for all the projects assigned to the
contractor and display a "OVER BUDGET" line if they're out of sync.

Mark Flippin

Mark Flippin <me321701 (AT) comcast (DOT) net> wrote

Mark
You can create a query that has a unique value for the estimated cost.
If the number is the same, it does not need to be listed in each
record necessarily. Insert the value from the query into your report.
Feel free to email me if you need more help.
Kara Hooper

ww.klh-tech.com
KLH Technology Solutions