Calculating Percentages

[Guest]

The SQL view of my query is as follows:

SELECT IIf([Order]>819000000,"PP02","PP01") AS Type, Sum([tblWIP].[Amount])
AS Amt
FROM tblWIP
GROUP BY IIf([Order]>819000000,"PP02","PP01");

How do I add a calculated field that will compute % of WIP $ by Order Type?

For Example:

Type Amount Pct
PP01 $45,000 56.25
PP02 $35,000 43.75

TIA,
Craig

 

[Duane Hookom]

Do this in a report where you can set a control source to:
=Amt/Sum(Amt)

 

[John Spencer (MVP)]

I'm not sure this will work, but I would try


SELECT IIf([Order]>819000000,"PP02","PP01") AS Type,
Sum([tblWIP].[Amount]) AS Amt,
Sum(TblWip.Amount)/(SELECT SUM(Amount) From tblWip) as PerCentage
FROM tblWIP
GROUP BY IIf([Order]>819000000,"PP02","PP01");