Sum detail line with criteria 2

[antvon]

Hi

I would like to do a aggregate sum on a detail line, but only where the detail lines
meet a certian criteria. ie.


Group A

Code year litres
001 2002 50
001 2003 25

002 2002 75
002 2003 50

003 2002 25
003 2003 25
-----------------
100
-----------------


Group B

Code year litres
101 2002 50
101 2003 75

202 2002 75
202 2003 250

303 2002 25
303 2003 25
-----------------
350
-----------------

Group C

Code year litres
301 2002 25
301 2003 50

302 2002 15
302 2003 50

303 2002 10
303 2003 25
-----------------
125
-----------------

-----------------
575
-----------------

As you can see the criteria is the year
My detail line groups by Code and the
Group category must only total litres for
year 2003 as well as all other groupings
so that I end up with a final total relevant
for the 2003 year

The 2002 year is just to compare this year with
the previous one

Unfortunately it seems that the aggregate functions
will not let me filter this way.

I have had some success using a second query to abtain
the group results and then insert a subreport linking
on the common fields, but I can only get the innermost
subrep to work, the others will not return any results

Does anyone have a suggestion.

Thanks in advance.

 

[SuicidED]

Hi

Put a text box in your goup footer with the following control source.
=Sum(IIf([year]=2003,[litres],0))
Eddie

 

[antvon]

Hi Eddie

Aaarrghhh

Thanks a million Eddie. Works like a dream.
I'm just upset with myself because I had used a formula
in the beginning but had coded it as
=IIf([year]=2003,Sum([litres]),0) and it would work
properly so I abondoned it.

Extremely grateful for your help

Anton