Creating a Total Of Transactions

[matthoward]

Good Morning,

I need to find myself a way to do a total of transaction types for a three month period. I have a report were I have grouped by account numbers and it shows that for example in the three month period they have made 2 direct debits and 1 by cheque. I need to be able to in the group footer put a total for Direct debits and Direct Credits for that particular account. Any one have any ideas?


Cheers Matt

 

[lbass]

If you create a second group on your account type field, then you can insert summaries, such as a count of transactions or sums of amounts within each type, etc., which will appear in your Group 2 footers.

Another approach, without an additional group, would be to create running totals and evaluate based on a formula which you set to a specific account type, e.g., {account.type} = "debit", reset on change of Group 1. You'd have to create one for each account type, and then you could place these in the Group 1 footer.

-LB

 

[Ngolem]

You can use the three formula method easily here

In the Group containing Account numbers place the following formula

@Initialize (placed suppressed in Acct # group)

WhilePrintingRecords;

If not in repeatedGroupHeader then
(
NumberVar TotalDirectDebitValue := 0;
NumberVar TotalDirectDebitCount := 0;
Numbervar TotalChequeValue := 0;
NumberVar TotalChequeCount := 0;
);



I am going to make some assumptions here about your database here and you will have to modify this accordingly...I am going to assume you have a field called DebitValue and another called DebitType (either direct or cheque)

@Calc (Placed suppressed in the detail section)

WhilePrintingRecords;

NumberVar TotalDirectDebitValue ;
NumberVar TotalDirectDebitCount ;
Numbervar TotalChequeValue ;
NumberVar TotalChequeCount ;

If {Table.DebitType} = "Direct" then
(
TotalDirectDebitValue := TotalDirectDebitValue +
{table.DebitValue};
TotalDirectDebitCount := TotalDirectDebitCount + 1;
)
else If {Table.DebitType} = "Cheque" then
(
TotalChequeValue := TotalChequeValue + Table.DebitValue};
TotalChequeCount := TotalChequeCount + 1;
);

Any other value of {Table.DebitType} will not calculate

Now in the Account Number footer you display the results

@DisplayValues (Placed in footer...not suppressed)

WhilePrintingRecords;

NumberVar TotalDirectDebitValue ;
NumberVar TotalDirectDebitCount ;
Numbervar TotalChequeValue ;
NumberVar TotalChequeCount ;
Stringvar Results := "";

Results := "A total of " + Totext(TotalDirectDebitCount,0) +
" Direct Debit deposits received value : $" +
Totext(TotalDirectDebitValue,2);
Results := chr(13) + chr(10);
Results := "A total of " + Totext(TotalChequeCount ,0) +
" Cheque deposits received value : $" +
Totext(TotalChequeValue ,2);
Results;

Within the limits of my assumptions that should do it for you...Make sure you allocate enough vertiacal space for the complete formula result.



Jim Broadbent

 

[matthoward]

Okay sorry guys I may not have made my self clear enough so here goes on attempt two. In my access datatabase at the moment I have two fields. Transaction Type and Account Number. The account number just lists every ones account number but the Transaction type lists the type of payment for example C for Cheque D for Cash etc etc. So below is an example
Account Number Transaction Type
32564564456 A
32564564456 A
32564564456 D
34289723879 F
34289723879 A

So for the crystal report it will show
32564564456 Transaction Type A = 2 Type B = 1 Type F = 0
34289723879 Transaction Type A = 1 Type B = 0 Type F = 1

Sorry for leading you astray guys and thanks for your great answers and effort anyway,

Cheers matt

 

[lbass]

The running total solution I suggested should work fine with your data depending on the overall display you want to achieve. Just select {AcctNo}, count, evaluate based on formula, e.g., for the type A running total:

{TransType} = "A"

Reset on change of Group 1 (AcctNo}. Repeat for each TransType, and place your results in the Group 1 footer. You can add text fields if you want to add captions like "Transaction Type A = " or even concatenate them with the running total in a separate formula. Then just suppress the details.

If the issue is that you want to display all your details and then all your summaries, you can create a crosstab with transaction type as the column and account number as the row and then choose count {AcctNo} as your summary field. Place the cross tab in your report footer.

-LB

 

[Ngolem]

My method works just as well.....just modify it to suit your situation

Jim Broadbent