none
Sultan soomro RRS feed

  • Question

  • Hello 

           Sir,

               how are you.

     i have Query like this

       ID   BuyerID         Date          Name               Narretion                             Debit          Credit        Balance

       1         1            1/1/2015       A                        Band DD                            0                  50               50

       1         2            1/1/2015       A                        Bank DD                            0                   30               80

       1         3            2/1/2015       A                         Invoice                            95                   0               -15

       2         4           3/2/2015        B                         Bank DD                            0                   50               35

       2         5           3/2/2015        B                         Invoice                             60                  0                 -25

      But I Want Like This Auto Genrate Balance

      

     ID   BuyerID         Date          Name               Narretion                             Debit          Credit        Balance

       1         1            1/1/2015       A                        Band DD                            0                  50               50

       1         2            1/1/2015       A                        Bank DD                            0                   30               80

       1         3            2/1/2015       A                         Invoice                            95                   0               -15

        2            4                   3/2/2015          B                                    Bank DD                                   0                            50                     50

       2         5           3/2/2015        B                         Invoice                             60                  0                -10

         I Want Name Wise Balance and when i enter new invoice or cash entery that time auto genrate query to like this all parties wise i have many parties please please please Help me i will greatfull to you Sir

    Wednesday, November 25, 2015 11:49 AM

All replies

  • Perhaps this, in SQL View:

    SELECT

    a.ID, a.BuyerID, a.[Date], a.[Name], a.Narretion, a.Debit, a.Credit,

    SELECT(SUM(Credit - Debit) FROM [YourTableName] b WHERE a.[Name] = b.[Name] AND a.[Date] <= b.[Date]) AS BalanceByName

    FROM [YourTableName] a


     


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Wednesday, November 25, 2015 12:23 PM
    Wednesday, November 25, 2015 12:22 PM
  • Sorry some Error

     i have many tables Query then union Query then again Query for balance 

    1 table customerDetail 

    Field name :   ID  Company  Phone Fax Mobile Station

    2 table CustomerOrder

    Field name : BuyerID  fkID (my fkID Relation with Customerdetail ID ) 

    3Table CustomerInvoice

    Field Name : ID  fkBuyerID (my BuyerID Relation with CustomerOrder BuyerID) Date Company Narretion Bales Weight Mounds Rate Amount Tax Loading Debit TotalAmount ( Its very large Invoice ) And i dont need Debit in Invoice table but i am typed here becouse my last table need debit table

    4 Table CustomerCashBook

    Field Name :ID CustomerID ( my customer id also relation with CustomerOrder Table  buyerID ) Date Company  Narretion  Debit   Credit

    now i think you will help me greatfully and help me about Balance sheet 

    Thursday, November 26, 2015 7:45 AM
  • Hello Sir

            how are you

    anybody help me about this topic pelase pelase pleaseeeeeeeeeeeeee?

    Friday, November 27, 2015 5:54 AM
  • >>>I Want Name Wise Balance and when i enter new invoice or cash entery that time auto genrate query to like this all parties wise i have many parties

    According to your description, It seems that you want to do it like "triggers" in Microsoft SQL Server, If so, I suggest that you could use event-driven Data Macros that are similar to triggers.

    Data macros are a new feature of Access 2010 which enable you to add logic to events that occur in tables, such as adding, updating, or deleting data. They are similar to "triggers" in Microsoft SQL Server.

    For more information, click here to refer about Create a data macro

    Friday, November 27, 2015 8:23 AM
  • Thanks For Reply

    Sir David

    hmmm Do you mean now i should work on macros?

    ok give your openion my relationship is ok or not ........ i have 4 tables and all detail you know that all 

    and can you help me on Macros 

    how i manage my table 

    i dont have any Idea pleaseee?

    Friday, November 27, 2015 10:19 AM