crosstab query calculate/divide iif... 1

[John1Chr]

Orgn total 09A 09B 09C 09E new col
B302 190 190 N/A
B603 257 150 75 32 12.45%
B604 82 82 No 09 base costs Yet
BM01 30 30 No 09 base costs Yet
IM01 200 200 N/A
N101 5 5 N/A
N601 124 124 N/A


I need a Crosstab query that'll give me an amount in a field that will calculate 09E/(09A + 09B + 09C) only if there is an amount in 09E. For example, for org B603 I should have 12.45% in a new column called PE%. All other orgs should be "N/A" (not applicable) or if there is something in 09E and nothing in 09A or 09B or 09C then "No 09 base costs Yet". Can this be done in a crosstab query?

 

[BigRed1212]

I don't know if it could be one in a crosstab query, but I would think a straight query with a nested iif could do it

Typed not tested.

select orgn, total, 09a, 09b, 09c, 09e, iif(09e>0, iif((09a>0 or 09b>0 or 09c>0),09e/(09a + 09b + 09c), "No 09 Base costs yet), "N/A") as [PE%]
from ...

 

[John1Chr]

I think the difficulty comes from the fact that the "09's" are in separate rows and therefore a challenge to relate as if it's the same record with different fields...not sure with this one.

 

[PHV]

What is the SQL code producing the result you've posted ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

PHV,

TRANSFORM Sum([tbl BP08 by Proj 2A].Amt) AS SumOfAmt
SELECT [tbl BP08 by Proj 2A].Orgn, Sum([tbl BP08 by Proj 2A].Amt) AS total
FROM [tbl BP08 by Proj 2A]
WHERE ((([tbl BP08 by Proj 2A].Approp) Like "09*"))
GROUP BY [tbl BP08 by Proj 2A].Orgn
ORDER BY [tbl BP08 by Proj 2A].Approp
PIVOT [tbl BP08 by Proj 2A].Approp;

 

[BigRed1212]

Oh, I didn't realize you had posted the results of a query. I thought you had posted your data.

 

[John1Chr]

My fault, Big Red.... I didn't make it clear.

 

[BigRed1212]

I should have known that if it was easy enough for me to answer something was wrong- LOL!

 

[PHV]

What about this ?
SELECT Orgn, Sum(Amt) AS total
,Sum(IIf(Approp='09A',Amt,Null)) AS [09A]
,Sum(IIf(Approp='09B',Amt,Null)) AS [09B]
,Sum(IIf(Approp='09C',Amt,Null)) AS [09C]
,Sum(IIf(Approp='09E',Amt,Null)) AS [09E]
,IIf(Sum(IIf(Approp='09E',1,0))=0,'N/A',IIf(Sum(IIf(Approp='09A',1,0))=0 OR Sum(IIf(Approp='09B',1,0))=0 OR Sum(IIf(Approp='09C',1,0))=0,'No 09 base costs Yet',Format(Sum(IIf(Approp='09E',Amt,0))/Sum(Amt),'PERCENT'))) AS [PE%]
FROM [tbl BP08 by Proj 2A]
WHERE Approp Like '09*'
GROUP BY Orgn

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

PHV,

I think it's close....not sure why it's not calculating the B603 field that should be 12.45%...it does have the base costs....09A and 09B.

 

[PHV]

OOps, sorry, try this instead:
SELECT Orgn, Sum(Amt) AS total
,Sum(IIf(Approp='09A',Amt,Null)) AS [09A]
,Sum(IIf(Approp='09B',Amt,Null)) AS [09B]
,Sum(IIf(Approp='09C',Amt,Null)) AS [09C]
,Sum(IIf(Approp='09E',Amt,Null)) AS [09E]
,IIf(Sum(IIf(Approp='09E',1,0))=0,'N/A',IIf(Sum(IIf(Approp='09E',Amt,0))=Sum(Amt),'No 09 base costs Yet',Format(Sum(IIf(Approp='09E',Amt,0))/Sum(Amt),'PERCENT'))) AS [PE%]
FROM [tbl BP08 by Proj 2A]
WHERE Approp Like '09*'
GROUP BY Orgn

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

U nailed it!

I would be embarrassed to tell u the amount of time I spent on that one without making any progress b4 posting....

 

[Remou]

Hi BrGenCAJ
Clicking the link above:

Thank PHV
for this valuable post!

Is a good way to thank a person who has been helpful.

http://lessthandot.com

 

[John1Chr]

I've taken your good advice on that one Remou....

 

[lespaul]

I would be embarrassed to tell u the amount of time I spent on that one without making any progress b4 posting.

Don't feel bad at all BrGenCAJ...PHV makes all of us look like we don't have a clue! He's the SQL GOD!!!

Leslie

Have you met Hardy Heron?