conditional sum

  • Thread starter Thread starter chriske911
  • Start date Start date
C

chriske911

wich function can I use for a conditional sum at the end of a report

number of records for type A, number of records for type B, and so on

thnx
 
chriske911 said:
wich function can I use for a conditional sum at the end of a report

number of records for type A, number of records for type B, and so on

thnx
Click to expand...

You can use a text box with a controlsource like this:

=Abs(Sum([RecordType]="A"))

The evaluation of the logical expression [RecordType]="A" for each
record results in a value of True (-1) or False (0). Those values are
then summed, giving a negative count of True values, which the Abs()
function converts to a positive number. You could also use

=-Sum([RecordType]="A")

but I prefer Abs() as it would still work in database systems that use 1
for True, instead of -1.
 
I ment a sum of an invoice where the record contains a field of type A and
the seme sum but now for type B
it's to split up grand totals of a report into the specific types

thnx
Dirk Goldgar said:
chriske911 said:
wich function can I use for a conditional sum at the end of a report

number of records for type A, number of records for type B, and so on

thnx
Click to expand...

You can use a text box with a controlsource like this:

=Abs(Sum([RecordType]="A"))

The evaluation of the logical expression [RecordType]="A" for each
record results in a value of True (-1) or False (0). Those values are
then summed, giving a negative count of True values, which the Abs()
function converts to a positive number. You could also use

=-Sum([RecordType]="A")

but I prefer Abs() as it would still work in database systems that use 1
for True, instead of -1.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
chriske911 said:
I ment a sum of an invoice where the record contains a field of type
A and the seme sum but now for type B
it's to split up grand totals of a report into the specific types
Click to expand...

Ah, that's not what you said. Sure, that's not much more complicated.
I'm assuming you haven't grouped your report by RecordType, or else you
could put simple subtotal boxes in the group footers and redisplay their
values in the report footer. So instead you could use either of these:

=Sum(IIf([RecordType]="A", [Amount], 0))

or

=Abs(Sum(([RecordType]="A")*[Amount]))
 
thnx, I almost never bother with reports, I leave that up to the commercial
wizzards, normally I just build structure and design but lately it's been
getting to much for their knowledge and data is getting to much to handle in
excel anymore, so again thnx for your help

grtz
Dirk Goldgar said:
chriske911 said:
I ment a sum of an invoice where the record contains a field of type
A and the seme sum but now for type B
it's to split up grand totals of a report into the specific types
Click to expand...

Ah, that's not what you said. Sure, that's not much more complicated.
I'm assuming you haven't grouped your report by RecordType, or else you
could put simple subtotal boxes in the group footers and redisplay their
values in the report footer. So instead you could use either of these:

=Sum(IIf([RecordType]="A", [Amount], 0))

or

=Abs(Sum(([RecordType]="A")*[Amount]))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top