Results 1 to 6 of 6
  1. #1
    Ashik2020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    6

    Now I want to generate Ledger statement for each client from my Transection Table through MS Access

    Dear Experts,
    I have a Table named as “Transection Table” in MS Access 2016 as follows:

    Transection Table
    TransectionID TransectionDate TransectionType Amount ClientID
    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 each client from my Transection Table through MS Access Query which would generate Ledger statement report like Below:

    Ledger Statement Report
    Opening Balance ClientID TransectionDate TransectionType Amount Closing 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 appropriate SQL query and report. Your assistance will be much appreciated !! Thanks In Advance !!
    Ashik


    Dhaka , Bnagladesh

  2. #2
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Now I want to generate Ledger statement for each client from my Transection Table through MS Access Query which would generate Ledger statement report like Below:
    if you want a report for each client, why is there more than one client listed in your example?

    the word you want is transaction.


    good luck with your project,


    Cottonshirt

  3. #3
    Ashik2020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    6
    Dear Cottonshirt, Sorry for the spelling mistake, And I mentioned "for each client " but actually, I meant all the clients like the sample report showing, sorry for this confusion too. However, suggest me a proper query, if you are willing to help me. Thanks in Advance.

  4. #4
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    the question you've asked makes very little sense from either a financial perspective or a database perspective.

    if all you are doing is listing every transaction and showing the balance before and after each transaction, then this can be most simply achieved in a spreadsheet. all you would have to do is sort them into date order. spreadsheets were designed to do this sort of stuff. you don't even need a database to do this.

    if I were to answer the question you have actually asked, then a year from now you will have ten thousand transactions and a report with ten thousand rows that is virtually incomprehensible to a human. and you would be disappointed and I would have wasted my time.

    from a finance perspective you need to introduce some sort of periodicity into the report. in other words, you would need a report that shows each client's balance this month. you would have their brought forward balance from the previous month, the sum of all invoices issued this month, all payments made this month, and their balance. that sort of thing is way hard for spreadsheets but easy for a database to do. but to do that you would need more than just a transaction table and an SQL query or two, you would need a proper database.

    good luck with your project,


    Cottonshirt

  5. #5
    Ashik2020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    6
    Brother, I have done It by myself !! but I had to change the table structure a little bit.... see the below query plz:

    SELECT ( Nz ( (SELECT Sum(DrAmount-CrAmount) FROM LedgerData as D2 where D2.ClientID=LedgerData.ClientID and D2.TransectionDate<LedgerData.TransectionDate) ,0) ) AS OpeningBal, LedgerData.ClientID, LedgerData.TransectionDate, LedgerData.TransectionType, LedgerData.DrAmount, LedgerData.CrAmount, (SELECT Sum(DrAmount-CrAmount) FROM LedgerData as D where D.ClientID=LedgerData.ClientID and D.TransectionDate<=LedgerData.TransectionDate) AS CBalance
    FROM LedgerData
    ORDER BY LedgerData.ClientID, LedgerData.TransectionDate;

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Cross post https://stackoverflow.com/questions/...ansection-tabl

    This is a common topic. Search web and will find many discussions. One approach uses DSum() to get Start Balance in report or group header and RunningSum property of textbox on report to calculate daily balance.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 08-08-2019, 05:15 PM
  2. Replies: 1
    Last Post: 03-29-2014, 10:19 AM
  3. Replies: 1
    Last Post: 07-31-2013, 11:47 PM
  4. Replies: 1
    Last Post: 07-26-2011, 06:10 AM
  5. iif statement to generate date
    By Theremin_Ohio in forum Access
    Replies: 8
    Last Post: 05-31-2011, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums