Balances in accounting database
I have a database that has financial data, (deposits, items paid and encumbered) for 6 seperate types of accounts. I have set up a tables to give me the correct account information according to that type code (2,4,6 or7). Now I need to develop a layout that I can get balances to calculate when a user chooses an account to view the detailed account actions on.
The data entry all goes into one table right now, so I can not use the index number tied to the record that has been suggested here in the past. I have tried to get a found set(by account number) and If the record number is not 1 then use the GetNthRecord(this record number -1) to get the previous balance from the record above it. This is not working. Does anyone have a database like this and how have you created a running balance in this type of environment? Any help would be great!
Assuming you have a table of Accounts, you can show the account's balance there by a using a calcuation field =
Sum ( Transactions::Amount )
You can also define a summary field in the Transactions table as Total of Amount (can be running total, if you so prefer).