conditional sum

[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

 

[Dirk Goldgar]

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

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.

 

[chriske911]

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

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

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)

 

[Dirk Goldgar]

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

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]))

 

[chriske911]

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

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

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)