Conditional Sums for a report

[Peter Bellamy]

I am using a simple query for a report
The query produces a value for each line by multiplying a quantity by a cost. Total Value: [cost] * [quantity]

I want to also obtain the sum of these Total Value(s) between set figures to use in the report. For example:
Sum of Total Values between 0 and 10.00
Sum of Total values greater than 10.00 and less than 100.00
Sum of Total Values greater than 100.0 and less than 1000.00
etc.

I have tied a few expressions in the query but I don't think I am using the correct syntax.
Any suggestions?

Cheers
Peter

 

[Brianwarnock]

Sounds like you are trying to do it all in 1 query which you cannot.
The second query will be an aggregate query

Sumoftotalsbetween0and10:Sum(IIF(Totalsum<=10,[Totalsum],0))

etc

This could then be a subreport in the Report footer

Brian

 

[Peter Bellamy]

Thanks Brian.

That is ok except I have some negative values so it has to really be >0, <10.
Rather than inferred by just <10.

I suppose I could do a <0 and subtract but that seems a bit clumsy!

Peter

 

[Brianwarnock]

Many of the IIFs will be > And <= or similar.

Brian

 

[Peter Bellamy]

Thanks
This seems to work
Sum0to10: Sum(IIf([Total Value]>0 And [Total Value]<=10,[Total Value],0))

Can Count be used in a similar way to count the number of items summed?

Cheers

Peter

 

[Brianwarnock]

Don't see why not, give it a try.

Brian

 

[Peter Bellamy]

I tried :
Count0to10: Count(IIf([Total Value]>0 And [Total Value]<=10,[Total Value],0))

But it returns the same value whatever the 'range' in the if statement, 1070.
So it seems not!

 

[Brianwarnock]

Sorry wasn't thinking straight, the way you have to do this is with a Sum.

Count0to10: Sum(IIf([Total Value]>0 And [Total Value]<=10,1,0))


Brian

 

[Peter Bellamy]

That's neat, thank Brian

Peter

 

[Brianwarnock]

Glad I could correct my error early

Brian