Help on report footer calculated field

  • Thread starter Frankie via AccessMonster.com
  • Start date
F

Frankie via AccessMonster.com

I have the following issue:
On a report footer based on a query, grouped by [Type](Type being "in" and
"out"), with totals by [Type] in group footer and report footer, I want to
add a calculated field that show the difference between the totals of "In"
and "out".
I read threads about this but don't really get it.
Any help would be greatly appreciated.
Thanks in advance.
Access 2003
 
A

Al Camp

Frankie,
Not sure if I understand but...

How about a field in your report query like this
IIF(Type = "IN",1,-1)
and then sum that field in the footer?
Might need a bit more info on your setup, but maybe this will fly...
hth
Al Camp
 
F

Frankie via AccessMonster.com

Al,

Thanks for your answer.
Sorry for the lack of detail.
The records with the label "In" have a total value that I wa
 
F

Frankie via AccessMonster.com

Al,

Thanks for your answer.
Sorry for the lack of detail.
The records with the label "In" have a total value displayed in the group
and report footer that I want to substract from the total value of the
records with the label "out" displayed in the same areas.
When I try a Sum function between the two fields It just doesn't work.
Any Idea ??
Thanks again
Frankie
 
A

Al Camp

Frankie,
As I said, I'm not sure I understand your setup...
Let me make sure you got what I was trying to do, (I'll try a slightly
diffrent approach) and if that doesn't work, please feel free to do a new
post. (with as much detail, or even a few sample typed out examples of your
data
)
You wrote...
In the query behind the report create 2 calculated columns...
CountIN : IIF([Type] = "IN", 1, 0)
and
CountOUT : IIF([Type] = "OUT", 1, 0)

Now... in any footer (Group footer or Report Footer)...
= Sum(CountIN) would yield the number of INs and
=Sum(CountOUT) would yield the number of OUTs.
So... = Sum(CountIN) - Sum(CountOUT) would yield the difference.

hth
Al Camp
 
F

Frankie via AccessMonster.com

Al,
Sorry for the late answer, I was away for a couple of days.
Your help led me to the solution and I wonder why it didn't hit me before.
I was actually trying to sum 2 fields already summed up but you put me back
on track.
Thanks again for your help
Frankie
 
Top