Results 1 to 4 of 4
  1. #1
    muhammadirfanghori is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    6

    General Ledger Query With Opening Balance and Running Balance

    Hello,
    i have Two query 1st "Ledger Purchase Query Final" 2nd "Ledger Payment Query"
    i have union query Name Ledger
    SELECT [InvoiceID] AS [TransactionID], [SupplierName], [InvoiceDate] AS [TransactionDate], [Item Name] AS [Description], [Sub Total] AS [Debit], 0 AS [Credit] FROM [Ledger Purchase Query Final]


    UNION SELECT [VoucherID], [SupplierName], [InvoiceDate], [Description], 0 AS [Debit], Format([Amount], "Currency") AS [Credit] FROM [Ledger Payment Query]
    ORDER BY [TransactionDate];

    How can i get opening balance at specific date with runningsum
    like this,

    TransactionID TransactionDate SupplierName Description Debit Credit Balance
    Opening Balance 5000
    1 11-03-2015 ABC Receive material 10000 0 15000
    2 11-03-2015 ABC Paid Cash 0 8000 7000
    3 12-03-2015 ABC Receive Material 4000 0 11000

    Attached required database table and queries
    please help me...
    Attached Files Attached Files

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ledger.zip

    Your database with a few new items, note this is not really the way I would have done it but I am using your objects with the minimum of work for me

    1. qryLedgerReport modifies your union query to only include items up to the end date stated on the form frmReportSetup. NOTE the query summarizes all data prior to the start of the period entered into one line as your 'opening balance'
    2. frmReportSetup enter a start date and end date for the report
    3 rptledgerreport will open on the button click. note the BALANCE field has the RUNNING SUM property set to OVER GROUP (your customers/vendors).

  3. #3
    muhammadirfanghori is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    6
    Thanks for reply
    i m using Ms Access 2007
    that file you attach cannot open in my access 2007

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ledger.zip

    Try this

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

Similar Threads

  1. Replies: 8
    Last Post: 07-07-2014, 11:21 AM
  2. Replies: 3
    Last Post: 04-19-2014, 02:35 AM
  3. Replies: 1
    Last Post: 03-29-2014, 10:19 AM
  4. Replies: 8
    Last Post: 03-21-2014, 04:31 AM
  5. Ledger Report with opening balance
    By Mahavir in forum Access
    Replies: 7
    Last Post: 01-10-2012, 03:40 AM

Tags for this Thread

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