Calculating sums on values not in group

[chickenparmesan]

Hi everyone

Not sure how to explain my question without first giving a quick
overview of my data.

My table is set up in the following format.

Month > Payment Status > Payment Type > CheckAmount

Payment Status can either be Valid or Invalid
Only valid payments can have one of 3 payment types and a
corresponding amounts.

So I set up my report to group based on Month, then Valid Payments. It
shows the three totals of the PaymentTypes for each month. I have a
graph which is able to show the totals of both Valid and Invalid
payments, but I can't figure out how to make two text boxes which will
show that information.

The textboxes need to be in the Month footer. If I put
=Count([PaymentStatus]) I'm obviously getting the sum for both for the
entire month. How can I create two text boxes and specify that one
should only pull the count for Valid records and one should only pull
the count for Invalids? Thanks

 

[Marshall Barton]

Hi everyone

Not sure how to explain my question without first giving a quick
overview of my data.

My table is set up in the following format.

Month > Payment Status > Payment Type > CheckAmount

Payment Status can either be Valid or Invalid
Only valid payments can have one of 3 payment types and a
corresponding amounts.

So I set up my report to group based on Month, then Valid Payments. It
shows the three totals of the PaymentTypes for each month. I have a
graph which is able to show the totals of both Valid and Invalid
payments, but I can't figure out how to make two text boxes which will
show that information.

The textboxes need to be in the Month footer. If I put
=Count([PaymentStatus]) I'm obviously getting the sum for both for the
entire month. How can I create two text boxes and specify that one
should only pull the count for Valid records and one should only pull
the count for Invalids?

=Sum(IIf(PaymentStatus="Valid", 1, 0)
=Sum(IIf(PaymentStatus="Invalid", 1, 0)