Multiple Sum IIF statements 2

[JStrand]

I am trying to combine multiple Sum IIF calculations in one report to combine the results of multiple departments. The first department works well using:

=(Sum(IIf([ReportGroupBook]="11Budget",[1]/1000,Null)))

What is the syntax to combine other departments into this control source on a report? I would like to combine the totals of field [1] where ReportGroupBook = "12Budget", and "14Budget". Thank you.

 

[PWise]

try

=(Sum(IIf([ReportGroupBook] in ("12Budget","14Budget"),[1]/1000,Null)))

 

[dhookom]

I would try something like:

=Sum(Abs([ReportGroupBook] IN ("12Budget","14Budget")) * [1]/1000)

It would be [red]much much better[/red] if you stored something in your tables that identified 12Budget and 14Budget as related rather than hard-coding values into expressions. Values belong in your tables and not in your code.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thanks to you both for your ideas! Duane I agree I'd rather not hard code into the expressions as you mention. The 12Budget is a combination of values denoting a range of P&L line items (1), Ctr (2), and budget for budget. 12Budget. I have these 3 fields in my union query for the data source but am trying to figure out a better method for combining and filtering the data in the report. The concatenation helped me distinguish the budget from actual data although I should probably use a better method.

I've been testing nested loops with synatx below and getting a the same result as your suggestion. Would you consider this a better method?

=Sum(IIf([Ctr]<9,IIf([ScorecardLine]<=4,IIf([ActBud]="Budget",([1]/1000*-1),0),Null)))

Any additional suggestions for summing the budget and actual data with the relationships that I have described would be apprerciated?

 

[dhookom]

With this you still have 9 and 4 hard-coded.

Sum(IIf([Ctr]<9,IIf([ScorecardLine]<=4,IIf([ActBud]="Budget",([1]/1000*-1),0),Null)))

It might be difficult storing this type of information in your tables.

Duane
Hook'D on Access
MS Access MVP