I want to generate Ledger statement Report for All the clients from my Transaction Table through MS Access Query

sharif007

New Member
Joined
Jul 6, 2014
Messages
4
Dear Experts,

I have a Table named as “Transaction Table” in MS Access 2016 as follows:



Transaction Table​
TransectionTransection DateTransectionTypeAmountClientID
1​
09-06-20​
Bill
4000​
2​
2​
12-06-20​
Bill
1000​
1​
3​
16-06-20​
Bill
45670​
4​
4​
16-06-20​
Bill
45670​
5​
5​
24-06-20​
Payment
45670​
5​
6​
15-07-20​
Bill
3000​
3​
7​
17-07-20​
Payment
500​
1​
8​
18-07-20​
Bill
1000​
1​
9​
20-07-20​
Bill
5000​
1​
10​
20-07-20​
Payment
2500​
3​
11​
22-07-20​
Payment
1234​
4​
12​
25-08-20​
Bill
5000​
3​


Now I want to generate Ledger statement for all the client from my Transaction Table through MS Access Query which would generate Ledger statement report like Below:

Ledger Statement Report
Opening BalanceClientIDTransactionDateTransectionTypeAmountClosing Balance
0​
1​
12-06-20​
Bill
1000​
1000​
1000​
1​
17-07-20​
Payment
500​
500​
500​
1​
18-07-20​
Bill
1000​
1500​
1500​
1​
20-07-20​
Bill
5000​
6500​
0​
2​
09-06-20​
Bill
4000​
4000​
0​
3​
15-07-20​
Bill
3000​
3000​
3000​
3​
20-07-20​
Payment
2500​
500​
500​
3​
25-08-20​
Bill
5000​
5500​
0​
4​
16-06-20​
Bill
45670​
45670​
45670​
4​
22-07-20​
Payment
1234​
44436​
0​
5​
16-06-20​
Bill
77566​
77566​
77566​
5​
24-06-20​
Payment
77566​
0​


Please anyone do this for me by suggesting an appropriate SQL query and report. Your assistance will be much appreciated !! Thanks In Advance !!

Ashik
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,916
Office Version
  1. 2016
Platform
  1. Windows
Use PowerQuery: From the Get & Transform tab of the Data Ribbon choose 'New Query', 'From Database', 'From Microsoft Access Database'. You can do any translation you need in PQ and then use PowerPivot (if necessary) to show the output.

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,114,521
Messages
5,548,541
Members
410,846
Latest member
rafferty1702
Top