I have two tables like that.
tblPurchase:
|
AccountID
|
InvNo
|
P_Date
|
ItemCode
|
Price
|
Qty
|
NetBal
|
|
A
|
Inv-1
|
11.11.2021
|
A
|
100
|
2
|
600
|
|
A
|
Inv-1
|
11.11.2021
|
B
|
100
|
2
|
600
|
|
A
|
Inv-1
|
11.11.2021
|
C
|
100
|
2
|
600
|
|
B
|
Inv-2
|
11.11.2021
|
A
|
100
|
1
|
200
|
|
B
|
Inv-2
|
11.11.2021
|
B
|
100
|
1
|
200
|
Account holder A purchased some items an amount of Rs. 600
Account holder B purchased some items an amount of Rs. 200 as you can see in the table.
Now I also want to update the Account status of Account Holder A and Account Holder B. here is the table tblAccount
|
AccountID
|
Balance
|
Address
|
|
A
|
2400
|
|
|
B
|
2000
|
|
|
C
|
1500
|
|
When an Account holder A purchased items of any price for example Rs. 600 Then this amount must be updated in his account at the tblAccount against Account holder A. (2400 + 600 = 3000)
When an Account holder B purchased items of any price for example Rs. 200 Then this amount must be updated in his account at the tblAccount against Account holder B. (2000 + 200 = 2200)
Now output in tblAccount Should be like that
tblAccount
|
Account
|
Balance
|
Address
|
|
A
|
3000
|
Abc
|
|
B
|
2200
|
Sky
|
|
C
|
1500
|
xyz
|
Please guide me how to get solution. If there is any better idea and structure then please guide me.
