I have a table in MS access (2016 version) name Transaction Table, given following:
Transaction Table |
ID | Transaction Date | Receive | Payment |
1 | 01-Mar-20 | 50 | |
1 | 02-Mar-20 | 40 | 70 |
2 | 03-Mar-20 | 400 | 100 |
2 | 04-Mar-20 | 300 | 400 |
3 | 05-Mar-20 | 500 | 0 |
3 | 06-Mar-20 | 200 | 0 |
1 | 07-Mar-20 | 476 | 300 |
2 | 08-Mar-20 | 390 | 300 |
1 | 09-Mar-20 | 0 | 500 |
2 | 10-Mar-20 | 300 | 0 |
2 | 11-Mar-20 | 0 | 500 |
3 | 07-Mar-20 | 500 | 400 |
3 | 08-Mar-20 | | 200 |
Now I want to write a query (SQL view window) or want to design a query through MS Access query design wizard, which will generate a report containing ID wise Sub total, Running total and grand total from the above table( sample output is given below). I know that It can be done through MS Access Report wizard but I do not want that, I just want to write a query (using rollup, cube, running total query etc ) like the way it is usually done in Oracle / MS sql server/My sql . Please anyone help me to write my desired query as following:
Customer Ledger Statement Balance Report |
| | | | |
ID | Transaction Date | Receive | Payment | Closing Balance |
1 | 01-Mar-20 | 50 | | 50 |
| 02-Mar-20 | 40 | 70 | 20 |
| 07-Mar-20 | 476 | 300 | 196 |
| 09-Mar-20 | 0 | 500 | -304 |
| Total | 566 | 870 | -304 |
2 | 03-Mar-20 | 400 | 100 | 300 |
| 04-Mar-20 | 300 | 400 | 200 |
| 08-Mar-20 | 390 | 300 | 290 |
| 10-Mar-20 | 300 | 0 | 590 |
| 11-Mar-20 | 0 | 500 | 90 |
| Total | 1390 | 1300 | 90 |
3 | 05-Mar-20 | 500 | 0 | 500 |
| 06-Mar-20 | 200 | 0 | 700 |
| 07-Mar-20 | 500 | 400 | 800 |
| 08-Mar-20 | | 200 | 600 |
| Total | 1200 | 600 | 600 |
| | | | |
| Grand Total | 2656 | 2170 | 486 |
Thanks In Advance !!
