Building Expression in Query

  • Thread starter Thread starter Alberta Rose
  • Start date Start date
A

Alberta Rose

Good day.

In my query I am looking to write an expression that performs mathematical
calculations between 3 tables/queries which are listed in this combination
query. I want to call the field "Margin" and I want it to make the following
calculation (I've put the name of the table or margin behind the field name
in this example for your reference only):

Margin = FinalPrice (tblContractInformation) - SumOfActualCost
(qryTotalCostsActual) - ActualCost (tblContractHoursCosts)

ActualCost has 3 "or" criteria listed which is 092110.5970 or 091110.5970 or
099990.4101. This is populated on my report footer as a subreport because I
couldn't figure out how to do the calculations with the Margin appearing in
the Report Footer section when I needed some informatin that resides in the
Detail section of the report in the calculation.

In the Detail area of my report, all expenses for this contract are listed
according to cost code/cost type combination and this works great. I have
totaled these costs in the Report Footer under "Distributed Cost" with
=Sum(IIf([costcode]="091110" Or [costcode]="092110" Or
[costcode]="099990",0,[ActualCost])).

So I guess the bottom line is: how do I reference a field in the Detail area
of my report in my Report Footer?

I know this is confusing, maybe not a good question for me to put out there
on a Monday :)
 
Are all three fields in the query?

If so, you should be able to enter something like the following in a field
"cell" in the query.

Field: Margin: [FinalPrice] - [SumOfActualCost] - [ActualCost]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
hmph, still stuck.

I have three fields in a subreport (in the report footer) called TSF, Admin
and Contract Price. The subreport query feeding this has a criteria in the
cost code field of >91100. I need to subtract the TSF (91110) and Admin
(92110) from the Contract Price (99990) and then subtract one field called
Distributed Cost (also in the report footer area of the report but with no
cost code) and populate the resulting balance into a field called Margin. I
was hoping there would be an easy expression to do this.


John Spencer said:
Are all three fields in the query?

If so, you should be able to enter something like the following in a field
"cell" in the query.

Field: Margin: [FinalPrice] - [SumOfActualCost] - [ActualCost]

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

Alberta said:
Good day.

In my query I am looking to write an expression that performs mathematical
calculations between 3 tables/queries which are listed in this combination
query. I want to call the field "Margin" and I want it to make the following
calculation (I've put the name of the table or margin behind the field name
in this example for your reference only):

Margin = FinalPrice (tblContractInformation) - SumOfActualCost
(qryTotalCostsActual) - ActualCost (tblContractHoursCosts)

ActualCost has 3 "or" criteria listed which is 092110.5970 or 091110.5970 or
099990.4101. This is populated on my report footer as a subreport because I
couldn't figure out how to do the calculations with the Margin appearing in
the Report Footer section when I needed some informatin that resides in the
Detail section of the report in the calculation.

In the Detail area of my report, all expenses for this contract are listed
according to cost code/cost type combination and this works great. I have
totaled these costs in the Report Footer under "Distributed Cost" with
=Sum(IIf([costcode]="091110" Or [costcode]="092110" Or
[costcode]="099990",0,[ActualCost])).

So I guess the bottom line is: how do I reference a field in the Detail area
of my report in my Report Footer?

I know this is confusing, maybe not a good question for me to put out there
on a Monday :)
Click to expand...
Click to expand...
 
Back
Top