Is it possible to use sum Function with criteria? 1

[bhoran]

I am using a report and I have say 2 rows of data

Plan P1 P2 Total
AOP 10 15 25
ACT 12 17 29

I want to use a formula in the report to divide AOP by ACT for P1 can I do this using a calculating control?

How do I do this?

Cheers

 

[PaulBricker]

I don't know if this will do it or not but you can try this in the Control Source for a textbox.

=IIf([Plan]=AOP,P1/IIf([Plan]= ACT,P1,0))

I haven't tried it but give it a shot and see what happens.

Paul

 

[bhoran]

If I add a value between the last 2 parethesis it return that value, otherwise it simply leaves it blank.

What I need to do is analyse the values in each column and depict the results in the rows underneath each group under the detail (i.e. in the group break footer)

Does anyone know how I can do this??

Perhaps I am taking the wrong approach?

If I was to calculate the values in using a series of queries could I then add them into the footer will they group/break the same away (asuming I use the same criteria) within the report ie will my calculated values for LPG 1 be in the footer of LPG1 in the 1st report and LpG2 in LPGfooter 2 etc etc?

Cheers

If I

 

[dhookom]

This is a reply to another of your threads with I assume the same issue.
[Green]A push into the proper direction. I assume the report is based on BHtest1CrossQueryRep. To sum the values in a field named [1] where a field name [LD]="AOP" you can use this expression:
=Sum( Abs([LD]="AOP&quot * [1]) )
This is much more efficient than
=DSum("[1]","BHtest1CrossQueryRep","[LD]='AOP'&quot
Also note the differences between my DSum() syntax and your attempt. [/Green]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bhoran]

Duane, I do see the difference in my Syntax for my DSum I will keep that in mind for the future.

I now have the calculations working fine for the other issue also using your abs formula, I assume this takes the[Plan] record (i.e. 1) and multiplies it by the value field [1]. So this would not work if there was more than 1 [Plan] record in that group?? That is fine for this excercise but I just want to get my head around it.

Thanks for all those that helped, you are godsends!

Cheers
bhoran

 

[bhoran]

You are right about the 3 threads sorry I just wasn't getting any answers so I thought maybe I made it all a bit complicated for people to understand.

Cheers