How To Create Running Total Formula In Access Query

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.

  1. Chart of Accounts : include all chart of account income, expenses, receivable and payable
  2. 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

create running total formula

Step 02. Create 2nd table with the name of tbl_transaction with following fields.

creating running total in access

Step 03. Enter the sample date in tbl_transaction table.

creating running total formula in access

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.

 

Database relationship

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. 

running total formula in access query

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.

running total formula in access query

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.

Leave a Reply