Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    HELP NEEDED: Running Balance with Data Containing NULL

    I need to calculate running balance on data containing Null, which is sorted by few columns.
    As example below, Clr is sorted by Null Desc, then by date, then just by ID.
    So far, I just come out with this...

    SELECT tr1.Clr, tr1.ID, tr1.Date, tr1.Acc, tr1.Dbt, tr1.Cdt, (SELECT SUM(Nz(tr2.Dbt) - Nz(tr2.Cdt)) FROM tbl00 AS tr2 WHERE (tr2.Acc = tr1.Acc AND tr2.Clr <= tr1.Clr)) AS Bal
    FROM tbl00 AS tr1
    WHERE (((tr1.Acc)=12345) AND ((tr1.Audit)=True))
    ORDER BY tr1.Clr, tr1.Date, tr1.ID;

    this query can give me the running balance for all data in Clr column not NULL, but when it come to null, the calculation stopped.

    Clr Date ID Acc Dbt Cdt Bal
    ----------------------------------------------------------------------------------------------------------------
    1 15/10/08 503 12345 100.00 NULL 100.00
    2 14/10/08 504 12345 NULL 3.00 97.00


    3 16/10/08 499 12345 NULL 150.00 -53.00
    4 16/10/08 505 12345 200.00 NULL 147.00
    5 18/10/08 506 12345 NULL 2.00 145.00
    NULL 18/10/08 500 12345 1.00 NULL 146.00
    NULL 19/10/08 499 12345 NULL 5.00 141.00

    i was also trying to break the result into 2 sql which 1 as above, calculate all the non-NULL result and another sql to calculate the remaining that contain NULL in Clr. In these tries, i trying to add a sequence column to replace the NULL in Clr column, but also unsuccessful.

    Any one can give me a hand???

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Perhaps I am missing something. What about having the Dbt and Cdt fields default to 0 or using Nz(tr2.Dbt,0)?

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    Perhaps I am the one who miss something, some more detail in my question.
    Actually, Nz is not the problem here. The calculation of BAL can be generate correctly, for the data where CLR is not NULL, but it stop after CLR reach NULL (since I sort Null value DESC). After CLR is NULL, I let the data sorted by DATE, then by ID. For this I try to add argument in (tr2.Acc = tr1.Acc AND tr2.Clr <= tr1.Clr) to continue the calculation, but of course, I failed.....

  4. #4
    Rhino373 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    65
    Why is CLR null? Can you use an update query to update the null values prior to running this "running balance" query?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    So you want to have one query that contains information for any record that has a CLR field filled in (Call this QUERY1). Then you want to have a second query where the CLR is blank but you want to inherit the balance from QUERY1 and continue to show the debits/credits based on the balance from QUERY1?

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

Similar Threads

  1. Running Queries from a form
    By HaYuM in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:12 AM
  2. Help needed desperately
    By tinyuna in forum Access
    Replies: 0
    Last Post: 05-03-2007, 02:18 AM
  3. not correct running total in group
    By cmk in forum Reports
    Replies: 1
    Last Post: 12-06-2006, 05:56 PM
  4. Txt box on form showing 'Balance'
    By wasim_sono in forum Programming
    Replies: 1
    Last Post: 04-25-2006, 05:58 AM
  5. Adding Running Balance from Form to Reports
    By KIDGEO3 in forum Reports
    Replies: 1
    Last Post: 01-18-2006, 08: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