Create Running Total Formula In Access Query
Create running total formula in access it will help in accounting system. Running total formula in access query need when you are creating accounting system. And you want to show the accounting statement which have the customer , supplier or other ledger transactions.
Before start to creating the running total query in access you must have the account data. Create the following two table in access.
- Chart of Accounts : include all chart of account income, expenses, receivable and payable
- Transaction Table : include daily transaction all accounts with debit and credit .
In previous tutorial we discussed about how to create a crosstab query in access 2016. Before starting you must understand what is database query in access. In a database a query is record selection from a single table or multiple table and process these selection into different report according to desire.
The database query is created by different purpose. You can make query for multiple field collection at once place from different tables, creating criteria or filter of data, create some function, formulas and much more.
Running Formula In Access Query 2016
The main aim of creating running total formula is calculate the running total balance of each account or product by sequence date wise or transaction wise. The running balance will change when transaction added. Following are the simple few steps for creating running total formula in query.
Step 01. Create table with the name of tbl_account. Like this
Step 02. Create 2nd table with the name of tbl_transaction with following fields.
Step 03. Enter the sample date in tbl_transaction table.
Step 04. Make relationship between tbl_account and tbl_transaction. account_id is primary key in tbl_account. And account_id is foreign key in tbl_transaction.
Step 05. Create database query of above two table. You can create query in access in two method first is creating query by wizard nd 2nd is creating query by design view. So we make query by design view.
Add the above two table in query design when you have added two table in design it will create automatically relationship between PK and FK.
Add the filed from the tables like following example. Then rename the tbl_transactio Alias with t1.Your query in design view look like this.
Step 06. Create Running Total Formula in last column with the name of Balance.
Step 06. Run the query by clicking on red sign at the top of left corner.
Now your running total formula is ready here you can make filter to track the account which you want. For example I want to track the electricity bill detail of each month. So I enter the electricity account_id in criteria.
Conclusion:
This running total formula in access query will help you in while you are developing accounting application in any database like ms access, sql server and my sql. The running balance formula will help you in managing running balance of customer r/a , supplier, inventory, expenses and much more.
