I would suggest a combined table for "in" and "out" transactions that use a debit and credit field for the two kinds of transactions. That then produces a unified table of transactions that you can use for your report. Here's a demo file: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12
Thank you very much!
I thank you again for your response, as you can see from the attached image, I have changed your solution to fit my particular situation, my only problem, is to forward the balance from the end of one month to the next.
I thank you again for all of your help!
You can use a calculation field with a relationship or a script that computes the balance forward that then sets a global number field with the balance forward. You can then put the balance forward in the header and replace the running balance field on your layout with a calculation field that adds the running balance field to the balance forward.
I tried using a calculation field, but I cant reference the sBalance(Final balance) field from the previous month
Correct, but you can use a relationship that matches to all records from previous months and then a Sum function can compute the sum of cBal and this will be your previous balance.
Note: I'm making up names as I go here so they may not exactly match the names in the demo file.
You might define a cMonth calculation field with Date selected as the result type: Date ( Month ( YourDateFIeld ) ; 1 ; Year ( YourDateFIeld ) ) to give all transactions from the same date, the date for the first of the month. Then a relationship such as:
Ledger::cMonth > Ledger|Previous::cMonth
Where "Ledger|Previous" is the name of a new Table occurrence of Ledger. Then, Sum ( Ledger|Previous::cBal ) will return the previous balance.