Bank Statement

How can i do random balance in ms access? as like attached file
Bank-Statement.xlsx
Mizanur RahmanExicutive AdminAsked:
Who is Participating?
 
NerdsOfTechTechnology ScientistCommented:
I assume you meant running balance?

First, you would import the records into a table. Let's call it `account` for these purposes.

I would normalize the transaction to (`Deposite`-`Withdraw`) and obtain the ultimate amount being debited or credited to the account (in query).

You would have to accumulate such transaction history from the beginning or track the balance (such as a daily balance table)

To find the balance of a given date you would use the date as the limiting factor.

Assuming you have no balance tracking table in place you would take the sum of all  (`Deposite`-`Withdraw`) transactions up to a certain date (for this example let's say we want the balance up to 1/6/18):


SQL View:

 SELECT SUM(TransAmt), acctNum
 FROM (
  SELECT  (a.`Deposite` - a.`Withdraw`) as TransAmt
  FROM account a
  WHERE 
  a.`date` <= #1/6/2018#
  AND a.acctNum = '0981234556'
 ) t1
 GROUP BY acctNum

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.