Calculation from one of multiple detail recs

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

To measure performance of our construction estimates vs.
actual contractor bids, we have a report consisting of
three subreports that shows project header information,
estimate data, and bid data. The latter two are multiple
detail records related to the project table via a foreign
key.

I would like a report control to calculate and print
the "Delta Percentage", i.e.,

<Bid - Estimate>/Bid * 100,

but only for the accepted bid (usually the low bid) and
the appropriate estimate (usually but not always the last
entered one). To handle the presence of these special
cases, we've added a Boolean field to both the Bid Data
and Estimate Data tables, which the user sets to True to
indicate that that record should be used in the Delta
calculation. Error checking ensures that only one detail
record in each table is selected for a given project.

My question is how to refer to the amounts in these
records in the calculation, both in a report, and on a
display form. Thanks for any assistance.

Sample Output (Delta in this case is (Low-CD)/Low):

Phase Amount Delta
=============== ============ =====
<Project> SD $1,250,000
<Location> DD $1,375,000
<Architect> CD $1,280,000
<Year> BID-LOW $1,265,000 -1.2%
- AVG $1,350,000
- HIGH $1,675,000
 
This is still a bit confusing. You should be able to pick up the awarded bid
amount by using an expression in the subreport's report footer:
=Abs(Sum([booAwarded] * [BidAmt]))
To get the final estimate
=Abs(Sum([booFinalEst] * [EstAmt]))
You can then refer to these to text boxes on your main report.
 
Back
Top