Report built on Union Query; Summing Fields Separately on Report

[athyeh]

Hi Folks,

I have a Union Query as follows:

SELECT qryAR.Operator, Sum(qryAR.P) AS SumOfP, qryAR.Pr
FROM qryAR
GROUP BY qryAR.Operator, qryAR.P
UNION SELECT qryAA.Operator, Sum(qryAA.P) AS SumOfP, qryAA.Pr
FROM qryAA
GROUP BY qryAA.Operator, qryAA.Pr
UNION SELECT  [qryNL].Operator, Sum([qryNL].["NL"]) AS SumOfP, "NL" AS Pr
FROM [qryNL]
GROUP BY [qryNL].Operator
UNION SELECT qryESum.Operator, Sum(qryESum.SLID) AS SumOfP, "E" As Pr
From qryESum
GROUP BY qryESum.Operator;

And have it presented in a report as follows:

SumOfProduction	Pr
131	R
180	NL
20	E
641	A
972

I am wondering, how can I manipulate the total at the bottom to show: R+NL+A OR E/(R+A+(NL*.2))?



Thanks for all your replies and insight.

[June7]

Expressions in textbox in footer section:

=Sum(IIf(Pr="R" Or Pr="NL" Or Pr="A", SumOfProduction, 0))

=Sum(IIf(Pr="E",SumOfProduction,0)) / (Sum(IIf(Pr="R" Or Pr="A",SumOfProduction,0)) + Sum(IIf(Pr="NL",SumOfProduction*0.2,0)))