Running sum with IIF ??

[CJ]

Hi Groupies!

I have a report that requires a running sum based on the contents of another
field.........

My report is sales grouped by customer per month. For this simplified
example lets say that we only sell either shoes or socks. The report is set
up as shown below:

MONTH HEADER
August
CUSTOMER HEADER
ABC Company
DEPARTMENT HEADER
Shoes
DETAIL
August 10 15 $30
August 11 20 $40
August 12 10 $20
DEPARTMENT FOOTER
45 $90
DEPARTMENT HEADER
Socks
DETAIL
August 11 30 $30
August 16 15 $15
DEPARTMENT FOOTER
45 $45 I am OK to this point!!

MONTH FOOTER
SHOES 90 $135 This is the problem point.
SOCKS 90 $135

So, I can generate a total for each department, but when I want to create a
total based on the department for the month, I run into trouble. I have
tried many different versions of IIF and various settings for the Running
Sum property, nothing works.

I thought that I needed something like:

=IIf([strDeptName]="Shoes",Sum([numQty]))

However, it is not working not matter what I do or where I place it. I have
also tried using the above formula in the detail section and then referring
to the control from the month footer, no luck.........I'm just not plugged
in to running sums....

Any help would be huge.
Thanks!

 

[Duane Hookom]

Use a subreport based on a totals query grouped by Month. Add the subreport
to the Month Footer and set the Link Master/Child to limit the subreport to
the appropriate month.

 

[CJ]

Thanks Duane. I had an epiphany after I posted and I thought of a totals
query, I haven't tried it yet though.

Thanks for replying and confirming that I was trying too hard.
CJ

Use a subreport based on a totals query grouped by Month. Add the
subreport to the Month Footer and set the Link Master/Child to limit the
subreport to the appropriate month.

--
Duane Hookom
MS Access MVP

Hi Groupies!

I have a report that requires a running sum based on the contents of
another field.........

My report is sales grouped by customer per month. For this simplified
example lets say that we only sell either shoes or socks. The report is
set up as shown below:

MONTH HEADER
August
CUSTOMER HEADER
ABC Company
DEPARTMENT HEADER
Shoes
DETAIL
August 10 15 $30
August 11 20 $40
August 12 10 $20
DEPARTMENT FOOTER
45 $90
DEPARTMENT HEADER
Socks
DETAIL
August 11 30 $30
August 16 15 $15
DEPARTMENT FOOTER
45 $45 I am OK to this
point!!

MONTH FOOTER
SHOES 90 $135 This is the problem point.
SOCKS 90 $135

So, I can generate a total for each department, but when I want to create
a total based on the department for the month, I run into trouble. I have
tried many different versions of IIF and various settings for the Running
Sum property, nothing works.

I thought that I needed something like:

=IIf([strDeptName]="Shoes",Sum([numQty]))

However, it is not working not matter what I do or where I place it. I
have also tried using the above formula in the detail section and then
referring to the control from the month footer, no luck.........I'm just
not plugged in to running sums....

Any help would be huge.
Thanks!