Q: I want to get Opening & closing balance from my Transaction Table in MS Access Query I have a table in MS access database with 
Table name : 
Transaction(*Trns_id, Trns_type, Tr_amount, Trns_date)  

The table data is below  
Trns_id Trns_type     Tr_amount Trns_date  
T001    Deposit       500       01-11-2016  
T002    Deposit       2500      01-12-2016  
T003    Withdrawal    2000      01-01-2017  
T004    Deposit       1500      01-02-2017  
T005    Deposit       1000      01-03-2017  
T006    Deposit       1500      04-03-2017  
T007    Withdrawal    1800      06-03-2017  
T008    Withdrawal    2000      04-02-2017  

I want to get a query with another two column Opening & Closing Balance
Opening of 1st row is 0 and opening of 2nd & next row is closing of previous row.
Closing calculated by 
    opening+(if trns_type=deposit then Tr_amount)- (if trns_type=withdrawal then Tr_amount)

Please MS Access query for this problem.
Output Like   

Trns_id Trns_type   Opening     Tr_amount   Trns_date   Closing  
T001    Deposit     0           500         01-11-2016     500  
T002    Deposit     500         2500        01-12-2016     3000  
T003    Withdrawal  3000        2000        01-01-2017     1000  
T004    Deposit     1000        1500        01-02-2017     2500  
T005    Deposit     2500        1000        01-03-2017     3500  
T006    Deposit     3500        1500        04-03-2017     5000  
T007    Withdrawal  5000        1800        06-03-2017     3200  
T008    Withdrawal  3200        2000        04-02-2017     1200 

Please help me
Thanks in advance

A: Query to pull values from previous record would be like:
SELECT *, (SELECT TOP 1 Tr_amount FROM Table1 AS Dup WHERE Dup.Trns_ID<Table1.Trns_id ORDER BY Trns_date DESC) AS PrevAmt, (SELECT TOP 1 Trns_type FROM Table1 AS Dup WHERE Dup.Trns_ID<Table1.Trns_id ORDER BY Trns_date DESC) AS PrevType FROM Table1;
Be aware this sort of nested query can perform slowly with large dataset.
Now use that query as RecordSource for report. Use textbox RunningSum property set to OverAll. Expressions in textboxes:
OpeningBal:
=Nz([PrevAmt],0)*IIf([PrevType]="Withdrawal",-1,1)
ClosingBal:
=[Tr_amount]*IIf([Trns_type]="Withdrawal",-1,1)
