3 Replies Latest reply on Apr 1, 2014 9:45 AM by philmodjunk

    Summary of accounts

    MikeMessenger

      Title

      Summary of accounts

      Post

           I apologize for my inexperience up front but I need some help. I have created a simple file to track revenue and expenditures of several accounts. In a summary report all of the accounts are there with their totals for each category but in the last column I want to show the balance for each account which should be the initial balance + revenue + expenditures but if I do this as a summary field I can't add the fields tougher and if it is a calculation field the total seems to include all accounts not just a specific account.

           I am sure there is a way to do this I just don't know how. Thanks for the help.

        • 1. Re: Summary of accounts
          philmodjunk

               How did you set up the fields in this table? One field for revenue, one for expenditures? Multiple revenue fields and multiple expenditure fields?

               How are you recording an initial balance? One more number field that is blank for all but the first transaction? Or ???

               The basic method that can be used in most situations is to define a calculation field that computes the "balance" for that one transaction and then define a summary field that summarizes this calculation field to get total balances and even running total balances. But there are also variations on this theme that use a running total summary field in a calculation to get what you need.

          • 2. Re: Summary of accounts
            MikeMessenger

                 Every transaction has a field to enter the account code, a field to enter a positive amount, a field for a negative amount and a transfer field that could be positive or negative. I needed to calculate total expenditures and revenue separate to find errors.

                 The initial balance for each account is in a separate table.

                 My sub-summary part has a field for the initial balance, summary of revenue, summary of transfers, and summary of expenditures. I was trying to add another field in the part that added all of these together to give a balance for the account.

            • 3. Re: Summary of accounts
              philmodjunk

                   I'd add a calculation field: Debit - Credit + Transfer. Then define a summary field that computes the total of this calculation field. But you can also use separate summary fields for each field if you want. The key is to use the getSummary function to access the total of a summary field as it is displayed in a sub summary layout part.

                   The syntax is: GetSummary ( SummaryField ; Breakfield ). For the break field, you select the same field as the one you have specified as the "when sorted by" field for you sub summary layout part.

                   So with my approach, your sub total calc could be:

                   RelatedTable::Starting Balance + GetSummary ( sTotalBalance ; AccountID ) If your sub summary part is sorted by Account ID

                   The only difference to using separate summary fields for each field is that you would then add together several getSummary function calls, one for each summary field instead of using just one as I have in this example.