Sultan soomro

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

Answer 1

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

Answer 2

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 

Answer 3

Hello Sir

        how are you

anybody help me about this topic pelase pelase pleaseeeeeeeeeeeeee?

Answer 4

>>>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

Answer 5

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?